Forum Discussion

Community Champion

5 years ago

Solved

Using Base DAX measures in DAX that breaks a baseline up incrementally to reach a target

This is the end result

The Figures below are currently simply hardcoded. So you start with 26 and over the years you want to go down to a target figure. What are the increments you need to go down by (Dependant upon the amount of years you add to the visual)?

Currently the DAX is this

Reporting Targets =

VAR LatestYr =

CALCULATE(

MAX('Date'[Year]),

ALLSELECTED('Date'[Year])

)

VAR EarliestYr =

CALCULATE(

MIN('Date'[Year]),

ALLSELECTED('Date'[Year])

)

VAR ReportTar = [Reporting hardcoded Target]

VAR Reportba = [Reporting hardcoded Baseline Average]

VAR breakdownPart =

DIVIDE(ReportTar - Reportba, LatestYr - EarliestYr)

RETURN

Reportba + breakdownPart * (MAX('Date'[Year]) - EarliestYr)

And this works perfectly bearing in mind we are just using the hardcoded values of 26 and 13

Its however more complicated than that because the Baseline and the Target have to be created from the actual data. If you create some DAX Like this

[Reporting Not hardcoded Target] =

VAR vBaseTable =

ADDCOLUMNS(

CALCULATETABLE(

VALUES('Date'[Year]),

'Date'[Baseline Year Flag] = TRUE,'Area Dim'[Area]="Example Area"

"tmpSum", [Metric]

)

VAR vResult =

AVERAGEX(vBaseTable,[tmpSum])

RETURN

vResult

And you add a card and look at this it provides the right answer, the one I want to use for the top level DAX

And the Targt would be a measure that simply halves the above measure

However as soon as you add them to the visual it all goes wrong

Because its applying all the filters from the below DAX rather than simply using it as a number 26. Is there any way I can implement this to get it working so the dev team dont have to hard code those figures for the reports any more.

Ive been attempting to figure this out for about a month and havent figured it out yet

Need Help

PaulDBrown
5 years ago
Hi DebbieE

I've been giving this a shot and I may have got somewhere. I'm not sure if this method will solve the problem (frankly I'm not all too sure what the issue you are encountering with the values (hard coded vs calculated) but let me walk through what I've done and see if it helps.

First the data and model:

The idea is to calculate the % change needed for each period (year) based on the starting date (year) and end date (year). So:

1) Calculate the value at the start date (which I guess in your real world would be based on a measure and not on the first and last dates in the dataset).

first value = VAR mindate = MINX ( 'Calendar Table', 'Calendar Table'[Date] ) VAR calc = CALCULATE ( [Sum Values], FILTER ( 'Calendar Table', 'Calendar Table'[Date] = mindate ) ) RETURN calc

2) Since we need this start value constant throughout, simply:

first value (all periods) = CALCULATE([first value], ALL('Calendar Table'[Year]))

To calculate the % change needed for each period, I used the function RRI. This function needs the number of periods over which the calculation must be calculated, and the starting value and target value.
3) So, firtsly, the number of periods (again this can be dynamic)

Calculate Periods = VAR last = LASTNONBLANK('Calendar Table'[Year], 'Calendar Table'[Year]) VAR first = FIRSTNONBLANK('Calendar Table'[Year], 'Calendar Table'[Year]) RETURN Last - first

4) and now the % change calculation

% change = VAR lastValue = CALCULATE([Sum Values], FILTER('Calendar Table', 'Calendar Table'[Date] = MAXX('Calendar Table', 'Calendar Table'[Date]))) VAR firstValue = CALCULATE([Sum Values], FILTER('Calendar Table', 'Calendar Table'[Date] = MINX('Calendar Table', 'Calendar Table'[Date]))) VAR calc = RRI([Calculate Periods], firstValue, lastValue) RETURN calc

In this example, the % change on each year to reach the target based on the baseline value is:

5) since we need this % change to cover all periods, simply:

% change ALL = CALCULATE([% change], ALL('Calendar Table'[Year]))

and finally we can create a measure to return the yearly values, from the starting value, where each year is calculated based on the % change:

6) Final measure:

Evolution = VAR currdate = MAX('Calendar Table'[Year]) VAR firstd = YEAR(MINX(ALL('Calendar Table'), 'Calendar Table'[Date])) VAR calc = [first value (all periods)] * POWER(1+[% change ALL], currdate - firstd) RETURN calc

The trick of course is that all date, value and period calculations should be aligned.

This is the final result:

I hope it helps!

4 Replies

v-lionel-msft
Community Support
5 years ago
Hi DebbieE ,

Please show me the sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- DebbieE
  Community Champion
  5 years ago
  Unfortunatly I dont have sample data
  - PaulDBrown
    Community Champion
    5 years ago
    Hi DebbieE
    
    I've been giving this a shot and I may have got somewhere. I'm not sure if this method will solve the problem (frankly I'm not all too sure what the issue you are encountering with the values (hard coded vs calculated) but let me walk through what I've done and see if it helps.
    
    First the data and model:
    
    The idea is to calculate the % change needed for each period (year) based on the starting date (year) and end date (year). So:
    
    1) Calculate the value at the start date (which I guess in your real world would be based on a measure and not on the first and last dates in the dataset).
    
    first value = VAR mindate = MINX ( 'Calendar Table', 'Calendar Table'[Date] ) VAR calc = CALCULATE ( [Sum Values], FILTER ( 'Calendar Table', 'Calendar Table'[Date] = mindate ) ) RETURN calc
    
    2) Since we need this start value constant throughout, simply:
    
    first value (all periods) = CALCULATE([first value], ALL('Calendar Table'[Year]))
    
    To calculate the % change needed for each period, I used the function RRI. This function needs the number of periods over which the calculation must be calculated, and the starting value and target value.
    3) So, firtsly, the number of periods (again this can be dynamic)
    
    Calculate Periods = VAR last = LASTNONBLANK('Calendar Table'[Year], 'Calendar Table'[Year]) VAR first = FIRSTNONBLANK('Calendar Table'[Year], 'Calendar Table'[Year]) RETURN Last - first
    
    4) and now the % change calculation
    
    % change = VAR lastValue = CALCULATE([Sum Values], FILTER('Calendar Table', 'Calendar Table'[Date] = MAXX('Calendar Table', 'Calendar Table'[Date]))) VAR firstValue = CALCULATE([Sum Values], FILTER('Calendar Table', 'Calendar Table'[Date] = MINX('Calendar Table', 'Calendar Table'[Date]))) VAR calc = RRI([Calculate Periods], firstValue, lastValue) RETURN calc
    
    In this example, the % change on each year to reach the target based on the baseline value is:
    
    5) since we need this % change to cover all periods, simply:
    
    % change ALL = CALCULATE([% change], ALL('Calendar Table'[Year]))
    
    and finally we can create a measure to return the yearly values, from the starting value, where each year is calculated based on the % change:
    
    6) Final measure:
    
    Evolution = VAR currdate = MAX('Calendar Table'[Year]) VAR firstd = YEAR(MINX(ALL('Calendar Table'), 'Calendar Table'[Date])) VAR calc = [first value (all periods)] * POWER(1+[% change ALL], currdate - firstd) RETURN calc
    
    The trick of course is that all date, value and period calculations should be aligned.
    
    This is the final result:
    
    I hope it helps!

Forum Discussion

Using Base DAX measures in DAX that breaks a baseline up incrementally to reach a target

4 Replies

Related content

Baseline Stock

Increment Refresh

Baseline Calculations

Incremental refresh

Incremental Refresh