• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. M

    Spread Variance to other Cells

    I have attached an example spreadsheet. I am trying to spread the variance into cells in column E via a formula. Currently it is manually. Cell F21 must equal 100%. Any ideas? Thanks!
  2. M

    Trend Function to Ignore Zeros

    I am trying to look at sales data for many stores. Some stores were not open at some points in time. For instance, maybe 3 stores did not open until 11/16 while others were open from 07/16 to present. I want to use the TREND function but want it to ignore the zeros. I did find a...
  3. M

    Power Query Use Data From Worksheet as Parameters

    I am pulling data from a database. What I would like to do is setup a few parameters in the workbook that contains the query and use named ranges to filter the data in the query. For instance have a start and end date, service, the beginning of a billing code such as PR, CB, or FB that I enter...
  4. M

    Calculate Trailing 12 Months - Power BI

    Yes the 1900 date is correct as some were entered that way into the DB. For retention it would be (4,759 - 37 )/5,294 = 89.2% My issue is how do I not count the one's that started in the period but also left within the period. I think it is going to take some tricky filtering.
  5. M

    Calculate Trailing 12 Months - Power BI

    Chihiro, I have attached an example excel file. The Date Table will be a slicer to control the dates. I'll need to do the calculations in columns J thru M. Column M will be the measure that will be displayed in Power BI. I am looking for a way to count between dates using DAX.
  6. M

    Calculate Trailing 12 Months - Power BI

    Anyone have any ideas on how to calculate a trailing 12 months in Power BI to create a graph showing how the client base has changed over time during a 12 month period? My calculation in Excel is Retention Rate = (End Bal. - New)/Begin Bal. I have data where there is a start date and an end...
  7. M

    Need advice on macro

    Thanks Kenneth! I'll play around with this and let you know. Mike
  8. M

    Need advice on macro

    Here is some example code I would use as a sub-macro: Sub AnicoFormat() 'This macro works Dim MyFolder As String, MyFile As String Dim ws As Worksheet, wb As Workbook MyFolder = "C:\Testing\Anico" MyFile = Dir(MyFolder & "\*.xl*") Do While MyFile <> "" Workbooks.Open...
  9. M

    Need advice on macro

    Kenneth: Yes there is a reason, of the 9 folders created (see the top of the code where all the "MkDir" are), there is a different format for the spreadsheets in each folder. So, with the code that I have, I am trying to figure out how to call a sub-macro to go through each of the files in...
  10. M

    Need advice on macro

    I have a macro that creates several folders, then takes data from one spreadsheet and creates several spreadsheets and saves them to a specific folder. Next I need to add several Call Macros to format each file in each folder, there are 9 folders, what is my best option? Should I setup a...
  11. M

    Create folder and save spreadsheets there based on criteria

    I figured it out. I changed this line of code: FN = LastClient & " " & strDate & ".xlsx" to this: FN = "\" & WSN.Range("N2") & "\" & LastClient & " " & strDate & ".xlsx"
  12. M

    Create folder and save spreadsheets there based on criteria

    I have a spreadsheet with data in columns A:N, headers are in row 1. Currently my macro takes the client name (there are 24 clients) in column N and creates a spreadsheet and saves it to a folder. In column N there are the names of the vendors associated with the clients. What I would like to do...
  13. M

    Power BI Date Ranges

    I figured out how to do this. I wrote SQL to do all the filtering, then simply pasted the SQL code in to Power BI. It works like a charm.
  14. M

    Power BI Date Ranges

    Just thinking here... what if I created two columns in the table SInvoices called startDate and endDate then using an IF statement, based on the SInvoice dates, I could calculate the commission rate and then use the slicer?
  15. M

    Power BI Date Ranges

    I have a table called SInvoices that has a column called invoiceDate. I am using a slicer to select a date range. I have another table called Commissions that has a column called startDate and another called endDate. I would like to have it when the slicer is used to select the date range it...
  16. M

    Power Query Need Some Filtering Help

    Thanks Chihiro. I believe I have figured it out. I took and loaded the table twice. On one I filtered on the end date. On the other I used Term. I then used Append and it seems to have worked. After that I made the two tables Connection Only and only the Appended data is showing.
  17. M

    Power Query Need Some Filtering Help

    I am kind of stuck and need an outside opinion. I have some data where the client has a specific term date, which is easy to filter. However, there is also a column where the client is either active or terminated. The issue is that in some cases the client is active but has a end date and in...
  18. M

    Power Query Filter Multiple Columns

    How do I create a filter that says IF column A begins with "C" and column D contains "Apples" then exclude that data from the query? Thanks!
  19. M

    Count rows between repeating values

    Thank for both replies. Chihiro's formula delivered the desired result. I had almost the same formula but used TRUE after INDEX. That was what threw me off. Again, thanks to both of you.
  20. M

    Count rows between repeating values

    In the example spreadsheet in column A is an ID# and in column B is the relationship. In row 2 there is the employee column B and their ID# in column A. In rows 3-5, they are related to the employee in row 2. What I am trying to do is in column C to have the ID# repeat until Employee is found...
  21. M

    Power BI Count between ranges

    OK I figured out how to do 0 to 25 in one calculation: 0 to 25 = countrows(CALCULATETABLE(Company,Company[EE Count] >0 && Company[EE Count] <=25)) So, how do I create a nested measure for all the groupings listed in my first post?
  22. M

    Power BI Count between ranges

    I have a table called Table1. In that table I have a company ID and a total count of employees. The fields are co and EE Count in the table. The count was summed up in the query itself. Now I want to break the EE Count into groups mentioned above.
  23. M

    Power BI Count between ranges

    I have a query setup with all the parameters needed. All I need now is to be able to display my data in groupings like this: 0 to 25 26 to 50 51 to 100 101 to 150 151 to 200 +200 What is the DAX counter part to COUNTIFS? Thanks, Mike
  24. M

    Question About Working Macro

    OK, there must have been something corrupt in the original macro. I copied the code from your post and put it in a new workbook and it works fine.
  25. M

    Question About Working Macro

    I get it right after I select the second file. I tried adding Application.DisplayAlerts but that messes up the formula. I'm running Excel 2010. It has me puzzled especially since you do not get that message.
Top