Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
5 years ago
Solved

Target and Current Measure, splitting between dates in a visual to see change over years

say you have a single measure. Baseline Average of 50

And you have another single measure, called target of 30

these are just single figure values

 

And you have a date table. 

Is there any way of dragging in years from the date table for example 2020 2021 2022 2023 and 2024 (5 years) into a visual

 

And have some DAX that figures out how you break down the 50 on the first year in 2020 to 30 in 2025

Which is 

but if you changed the number of years then the figures would update accordingly. 

 

obviously Ive gone for easy numbers here but essentially, someone could change the baseline and target figures and we could implement some DAX so we can add years in. 

 

At the moment I havent got much of a clue if it can be done. Im not actually sure of the maths at the moment so any ideas would be fantastic

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi DebbieE 

     

    I use some simple data to test and get what you need. Please take the following DAX codes for reference.

     

    • Date Table:
    Date = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2024,12,31)),"Year",YEAR([Date]))
    • Measures:
    Baseline Measure = 50
    Target Measure = 30
    Value Measure = 
VAR maxYear = CALCULATE(MAX('Date'[Year]),ALLSELECTED('Date'[Year]))
VAR minYear = CALCULATE(MIN('Date'[Year]),ALLSELECTED('Date'[Year]))
VAR breakdownPart = DIVIDE([Baseline Measure] - [Target Measure], maxYear - minYear)
RETURN
[Baseline Measure] - breakdownPart * (MAX('Date'[Year]) - minYear)

     

    I use a slicer to get the years from Date table. When changing the years range, the Value Measure will change accordingly. Let me know if this works at your side. Please download the PBIX.

     

    Best Regards,
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi DebbieE 

     

    I use some simple data to test and get what you need. Please take the following DAX codes for reference.

     

    • Date Table:
    Date = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2024,12,31)),"Year",YEAR([Date]))
    • Measures:
    Baseline Measure = 50
    Target Measure = 30
    Value Measure = 
VAR maxYear = CALCULATE(MAX('Date'[Year]),ALLSELECTED('Date'[Year]))
VAR minYear = CALCULATE(MIN('Date'[Year]),ALLSELECTED('Date'[Year]))
VAR breakdownPart = DIVIDE([Baseline Measure] - [Target Measure], maxYear - minYear)
RETURN
[Baseline Measure] - breakdownPart * (MAX('Date'[Year]) - minYear)

     

    I use a slicer to get the years from Date table. When changing the years range, the Value Measure will change accordingly. Let me know if this works at your side. Please download the PBIX.

     

    Best Regards,
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

    • DebbieE's avatar
      DebbieE
      Community Champion
      5 years ago

      thats fantastic,

      Thank you. Just need to try and figure out how it all works now