Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have a column named Date like below. The EARLIEST DATE is dyanmic, that is sometimes it can start with any month.
Date (MM/DD/YYYY)
09/01/2018
09/01/2018
09/01/2018
10/01/2018
11/01/2018
12/01/2018
01/01/2019
02/01/2019
I would like to keep only the next 6 Months of dates and then i will filter out all other rows. This formulas needs to be dynamic so the NEXT N months where N=6
I am thinking it can be done with an IF statement but not sure how to write it properly so would need some help here.
Another option I thought could be to Group by dates and filter only the next N months, but this method is extremely slow and mostly hangs because the dataset is large. The file can be found here
https://drive.google.com/open?id=1gyPL3-BALBqMWX1Mu4KasypS46nk7zUV
The query is named "STAT_APR"
Myabe the IF statement will work better?
Thanks
Solved! Go to Solution.
You may add step below.
= Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Date]) in each [Date] < Date.AddMonths(Date.StartOfMonth(earliest), 6))
You may add step below.
= Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Date]) in each [Date] < Date.AddMonths(Date.StartOfMonth(earliest), 6))
Hi,
Instead of the last Group by step, see if this line speeds up the process
#"Added Custom" = Table.AddColumn(#"Removed Columns", "First date", each List.Min(#"Removed Columns"[Date]))
This will give you the minimum date from the Date column. You may now take a difference of months between the Date and this new column and filter out all those >6.
If this is also slow then i guess the best way would be to load all the date and in the DAX formula, we try to exclude all dates beyond 6 months of the first date.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |