Calculating a measure using a weighing of the last 4 years

[adegrno]

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.

[OwenAuger · Accepted solution]

@adegrno 

Here is how I would recommend tackling this:
(I've uploaded a sample PBIX with dummy data here)

1. Ensure your model has a Date table (marked as date table) with a relationship to the date column of the table used to calculate the measure used for your KPI
2. Define the Base Measure that the KPI is based on.
   In my example this is simply:
   

   Base Measure = 
   SUM ( Sales[Sales] )

3. Define a 12-month rolling version of this measure. In my example this is:
   

   Base Measure Rolling 12 Months = 
   CALCULATE ( 
       [Base Measure],
       DATESINPERIOD( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
   )

4. Create a Weighting table in your model, with rows corresponding to each of the periods and their weightings. In my example, I created a table with columns Offset and Weighting, with Offset having values 0,-1,-2,-3 (years offset from the most recent year), and Weighting having values 0.4, 0.3, 0.2, 0.1.
   You don't have to create such a table, but it makes the code in the next step easier to read.
5. Define the final KPI Measure using SUMX to sum the rolling measure over the rows of the Weighting table. In my example this is:
   

   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

[adegrno]

Fantastic, this is exactly what I had in mind. Great to learn this!

Thanks for your help.