Skip to main content
cancel
Showing results for 
Search instead 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

Reply
adegrno
Advocate II
Advocate II

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
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

 

Thanks for your help.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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