Show last 13 months based on user single slicer selection

[JulietZhu]

I am working on month end report. The whole report is using last month as slicer. But also need show last 13 months trending file.

I find 1 online example (https://1drv.ms/u/s!AlYpYKwSuOKxhEcVysiK6Mh6XFz_) showing exact what I am looking for.

But with my data, after following all the steps from above online example, I can’t get the above visualization. Not sure why. Please help. Thanks.    https://1drv.ms/u/s!AlYpYKwSuOKxhEiZlxoSfSclawpb  (from my data)

[MFelix · Accepted solution]

Hi @JulietZhu,

The first variable of your Payment Last N is incorrect you are refering to the Payment_Amt however you want to calculate the Maximum date so you should refer to DateFilter column:

Payment (last n months) = 
VAR MaxFactDate =
    CALCULATE ( MAX ( DataFromDB[DateFilter]); ALL ( 'Date' ) ) 
VAR FDate =
    ENDOFMONTH ( 'Date'[Date] ) 
VAR Edate =
    EDATE ( FDate; -[N Value] ) 
RETURN
    IF (
MaxFactDate <= MAX ( 'Date'[Date])
&& MaxFactDate > Edate;
        CALCULATE ( SUM ( DataFromDB[Payment_Amt_Abs] ); ALL ( 'Date'  ) )
    )

Check below the result:

Regards,

MFelix

[JulietZhu]

@MFelix, you are absolute right. I took me 3 days to figure it out. Now you only took minutes. Thanks so so much.