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...
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.

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