Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |