Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix, you are absolute right. I took me 3 days to figure it out. Now you only took minutes. Thanks so so much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |