Using Filter and using in the last 12 months and only want 12 months on Column Chart

[bdehning]

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.    

[danextian · Accepted 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.

[v-xuxinyi-msft]

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.

[bdehning]

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?

[danextian · Accepted 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.

[bdehning]

I tried to use the measure,  but the DATEDIFF ('Calendar'[Date] will not take a column as it wants a measure? 

[danextian · Accepted solution]

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.