dax

How to calculate the delta between the previous period and the current period in DAX with an IF


I am trying to calculate in DAX the delta between the previous period (quarter) and the current period, but my formula does not work correctly for each case

I try to calculate the difference between Value and Q-1 Value when there is a result in Value <> 0 Then I try to calculate the difference between the Sale and the Value Q-1 when there is no result in Value = 0

To resume, here the formula explained

if the Value <> 0 => take the current Value - the Value of the previous Quarter else if the Value = 0 (case of a Sale) => take the sale - the Value of the previous period

I didn't succeed the second part of the if, it only shows the value of the Sale in current quarter

Here is the code I used:

Calculate =
IF (
    SUM ( 'Append'[Value] ) <> 0,
    SUM ( 'Append'[Value] )
        - CALCULATE (
            SUM ( 'Append'[Value] ),
            PARALLELPERIOD ( 'Append'[DATE_REFERENCE], -1, QUARTER )
        ),
    CALCULATE ( SUM ( Append[Sale] ), KEEPFILTERS ( 'Append'[Status] = "Sale" ) )
        - CALCULATE (
            SUM ( 'Append'[Value] ),
            PARALLELPERIOD ( 'Append'[DATE_REFERENCE], -1, QUARTER )
        )
)
  1. if the Value <> 0 => take the current Value - the Value of the previous Quarter

  2. if the Value = 0 (case of a Sale) => take the sale - the Value of the previous period```

Here is the result :

Code DATE_REFERENCE Status Sale Value Calculate
30 31/12/2023 Value 0 756527 -
30 31/03/2024 Value 0 756527 -
30 30/06/2024 Value 0 815173 58646
31 31/12/2023 Value 0 557133 -
31 31/03/2024 Sale -475000 0 -475000
31 30/06/2024 Value 0 0 -
32 31/12/2023 Value 0 351873 -
32 31/03/2024 Value 0 351873 -
32 30/06/2024 Sale -300000 0 -300000

Here is the result I would have :

Code DATE_REFERENCE Status Sale Value Calculate
30 31/12/2023 Value 0 756527 -
30 31/03/2024 Value 0 756527 -
30 30/06/2024 Value 0 815173 58646
31 31/12/2023 Value 0 557133 -
31 31/03/2024 Sale -475000 0 82133
31 30/06/2024 Value 0 0 -
32 31/12/2023 Value 0 351873 -
32 31/03/2024 Value 0 351873 -
32 30/06/2024 Sale -300000 0 51873

Could you please help me ?

Many thanks in advance


Solution

  • Using the code sent by Michal, I tested different formulas and now it works, I added the line "ALLSELECTED('Append')" followed by the line "PARALLELPERIOD('Append'[DATE_REFERENCE], -1, QUARTER)"

    This line allows me to select all the values, even those that were previously filtered and not retrieved by "PARALLELPERIOD"

    Here is the code

    CalculateMeasure = 
    VAR CurrentValue = SUM('Append'[Value])
    VAR CurrentSale = SUM('Append'[Sale])
    VAR PreviousQuarterValue = 
        CALCULATE(
            SUM('Append'[Value]),
            ALLSELECTED('Append'),
            PARALLELPERIOD('Append'[DATE_REFERENCE], -1, QUARTER)
        )
    RETURN
        IF (
            CurrentValue <> 0,
            CurrentValue - PreviousQuarterValue,
            IF (
                CurrentValue = 0,
                CurrentSale - PreviousQuarterValue,
                BLANK()
            )
        )