cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JulietZhu Member
Member

Show last 13 months based on user single slicer selection

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.

Capture.PNG

 

 

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)

Capture2.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Show last 13 months based on user single slicer selection

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:

 

rolling.gif

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Show last 13 months based on user single slicer selection

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:

 

rolling.gif

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




JulietZhu Member
Member

Re: Show last 13 months based on user single slicer selection

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