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.
Hi,
I wanted to calculate the difference between current month and last month, and only show the 13 months trend. The current month can be selected by a slicer.
These are my working steps:
1. Create a current measure:
Solved! Go to Solution.
Hi @changhongchoo ,
From this:
Assume my slicer value is now "Jul-21", I want to show the result from "Jul-20" to "Jul-21" only.
I think you want to display the value of last 13 month not the total difference of the last 13 month,right?
If so , please create a flag measure and apply it to visual-filter pane, set as "=1":
Flag =
var _sele=MAX('Date(for slicer)'[Date].[Date])
var _datediff=DATEDIFF(MAX('Table'[Date]),_sele,MONTH)
return IF( _datediff>=0 && _datediff<13,1,0)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @changhongchoo ,
From this:
Assume my slicer value is now "Jul-21", I want to show the result from "Jul-20" to "Jul-21" only.
I think you want to display the value of last 13 month not the total difference of the last 13 month,right?
If so , please create a flag measure and apply it to visual-filter pane, set as "=1":
Flag =
var _sele=MAX('Date(for slicer)'[Date].[Date])
var _datediff=DATEDIFF(MAX('Table'[Date]),_sele,MONTH)
return IF( _datediff>=0 && _datediff<13,1,0)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-eqin-msft ,
Thank you for the solution, I have solved the issue by following another reference.
https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=1079s
Regards
Choo
Hi,
I followed the exact steps but the result is blank..I have marked the date calendar as data table and join with the fact table.
@changhongchoo , With help from date table try measures like
Current = SUM('Acceptance Point'[value])
Previous =
CALCULATE(
SUM('Acceptance Point'[value]),
PREVIOUSMONTH('Date'[Date])
)
Last 13 month diff =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = eomonth(_max,-14)+1
return
calculate( [Current] - [Previous], filter('date', 'Date'[Date] >=_min && 'Date'[Date] <=_max ))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
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 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |