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.
I would like to limit my dataset to return only last two months availabe.
As my last month in a dataset is not the current month. For instance:
-current month is March 2019
-the latest month in the dataset is December 2018
So I would like to filter Dec 2018 and Nov 2018...
But I would like it to be dynamic. I mean, whenever my data grows and a new month be availabe, I would like to have always last 2 month from the latest one in the dataste.
Hope it is clear.
My date column looks like this:
and at this moment contains dates from Dec 2016 to Dec 2018
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Please try this query below. In my test file, I have the date from 2018/1/1 to 2018/12/31.
=Table.SelectRows(#"Changed Type", each [Date] > Date.AddMonths(List.Max(#"Changed Type"[Date]),-2))
Here is the output.
More details, please refer to my attachement.
Reference, you also could have a look at this similar thread.
Best Regards,
Cherry
Hi @Anonymous ,
Please try this query below. In my test file, I have the date from 2018/1/1 to 2018/12/31.
=Table.SelectRows(#"Changed Type", each [Date] > Date.AddMonths(List.Max(#"Changed Type"[Date]),-2))
Here is the output.
More details, please refer to my attachement.
Reference, you also could have a look at this similar thread.
Best Regards,
Cherry
I followed this formula and the resulting filter shows the last day of -3 month?
For example, the last date on my date column is 7/31/2021 and using the M function above, I should only show filters between 6/01/2021-7/31/2021 but I show 5/31/2021 - 7/31/2021. I think the function only manipulates the date and does not take in account the # of days in each month.
This is what I used to filter to true last 2 months:
"= Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNMonths([Date], 2))"
The M is working, thanks Cherry!
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |