Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |