cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Calculating a measure using a weighing of the last 4 years

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.

1 ACCEPTED SOLUTION
Super User

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

Owen Auger
Blog
2 REPLIES 2
Super User

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

Owen Auger
Blog

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors