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.
Hello,
in order to show a development for one of our kpi's we wish to weigh the value of a measure.
What I need to achieve is the following:
the value of the measure for the last 12 calendermonths shall weigh 0.4
the value of the measure for the 12 calendermonths before that shall weigh 0.3
the value of the measure for the 12 calendermonths before that shall weigh 0.2
the value of the measure for the 12 calendermonths before that shall weigh 0.1
The tables are always updated approx middle next month for the last month.
I have been scratching my head but I am a bit stuck, any suggestions in this forum?
Appreciate all help.
Solved! Go to Solution.
Here is how I would recommend tackling this:
(I've uploaded a sample PBIX with dummy data here)
Base Measure = SUM ( Sales[Sales] )
Base Measure Rolling 12 Months = CALCULATE ( [Base Measure], DATESINPERIOD( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) )
KPI Measure = SUMX ( Weighting, Weighting[Weighting] * CALCULATE ( [Base Measure Rolling 12 Months], DATEADD ( 'Date'[Date], Weighting[Offset], YEAR ) ) )
The KPI Measure will be evaluated "as at" the latest date filtered. So if you filter on the month February 2019, you will get 0.4 * Measure (March 2018 - February 2019) + 0.3 * Measure (March 2017 - February 2018)....
If you need the measure always evaluated as at the end of the last complete month, you could modify it with an appropriate date filter.
Hopefully that was some help - please post back if needed.
Regards,
Owen
Here is how I would recommend tackling this:
(I've uploaded a sample PBIX with dummy data here)
Base Measure = SUM ( Sales[Sales] )
Base Measure Rolling 12 Months = CALCULATE ( [Base Measure], DATESINPERIOD( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) )
KPI Measure = SUMX ( Weighting, Weighting[Weighting] * CALCULATE ( [Base Measure Rolling 12 Months], DATEADD ( 'Date'[Date], Weighting[Offset], YEAR ) ) )
The KPI Measure will be evaluated "as at" the latest date filtered. So if you filter on the month February 2019, you will get 0.4 * Measure (March 2018 - February 2019) + 0.3 * Measure (March 2017 - February 2018)....
If you need the measure always evaluated as at the end of the last complete month, you could modify it with an appropriate date filter.
Hopefully that was some help - please post back if needed.
Regards,
Owen
Fantastic, this is exactly what I had in mind. Great to learn this!
Thanks for your help.
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |