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 )
)
)
if the Value <> 0 => take the current Value - the Value of the previous Quarter
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
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()
)
)