The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a simple chart and using Loss Date (Date Field) in Filter to select Relative Date is in the last 12 months for a count measure (Count of Claims + 0 = CALCULATE(DISTINCTCOUNT(LossRun[Claim Number])) +0)
But since it is November 26, I am seeing 13 momnths and Nov 2022 and Nov 2023.
What can I add to eliminate the November 2022 and only see only the last actual 12 months.
Solved! Go to Solution.
Hi @bdehning ,
You can create a calculated column for the rolling number of months. In the sample below, the latest month is month number 1. You may modify __MAX_DATE to either reference TODAY() or the latest date in your table.
Rolling Month =
VAR __MAX_DATE =
MAX ( 'Calendar'[Date] )
RETURN
DATEDIFF ( 'Calendar'[Date], __MAX_DATE, MONTH ) + 1
You can then use this calculated column to filter a visual/page/report.
Proud to be a Super User!
It should worrk as a calculated column. Are you sure you're trying to create a calc column and not a measure? Please post a screenshot.
Proud to be a Super User!
Hi , @bdehning
You can create a measure as follows.
Measure = if(DATEDIFF(SELECTEDVALUE('Table'[Date ]), TODAY(), MONTH) < 12, 1 , BLANK())
Then add the measure to the filter, showing data with a measure of 1.
Is this the result you expect?
Best Regards,
Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried using this measure and it worked for 12 months. But if I changed the 12 to 24 or 36 and applied it to my filter, I would see 25 or 37 months?
Is there a way to allow for other number months than 12?
Hi @bdehning ,
You can create a calculated column for the rolling number of months. In the sample below, the latest month is month number 1. You may modify __MAX_DATE to either reference TODAY() or the latest date in your table.
Rolling Month =
VAR __MAX_DATE =
MAX ( 'Calendar'[Date] )
RETURN
DATEDIFF ( 'Calendar'[Date], __MAX_DATE, MONTH ) + 1
You can then use this calculated column to filter a visual/page/report.
Proud to be a Super User!
I tried to use the measure, but the DATEDIFF ('Calendar'[Date] will not take a column as it wants a measure?
It should worrk as a calculated column. Are you sure you're trying to create a calc column and not a measure? Please post a screenshot.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |