Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everybody,
I'm trying to calculate a Day On Day Change for a set of stocks. The thing is, when it comes to getting the previous day value, there are sometimes where Dax returns me a blank value since there's a weekend in between.
I guess this is because my formula has flaws... I'd like just to have the previous day value as it happens when it's labour day.
Formula and tables are:
Latest ULSD 10ppm MED (€/l) = CALCULATE (
AVERAGE('ULSD 10ppm CIF MED'[Litres EUR Prevat]),
FILTER ('ULSD 10ppm CIF MED',
'ULSD 10ppm CIF MED'[Date]
= CALCULATE (
LASTNONBLANK ( 'ULSD 10ppm CIF MED'[Date], 1 ),
FILTER (
'ULSD 10ppm CIF MED',
'ULSD 10ppm CIF MED'[Date] <= LASTDATE( 'ULSD 10ppm CIF MED'[Date])))))
PR ULSD 10ppm MED CIF Cargoes (€/l) =
IF(ISBLANK([Latest ULSD 10ppm MED (€/l)]),
[Latest ULSD 10ppm MED (€/l)],
CALCULATE (
AVERAGE('ULSD 10ppm CIF MED'[Litres EUR Prevat]),
PREVIOUSDAY('CALENDAR'[Date])))
DoD Change ULSD 10ppm MED CIF Cargoes (€/l) =
Var Division = DIVIDE([Latest ULSD 10ppm MED (€/l)] - [PR ULSD 10ppm MED CIF Cargoes (€/l)], [PR ULSD 10ppm MED CIF Cargoes (€/l)])
Return
Division
Expected Results are:
Date | Litres EUR Prevat (Current) | Prev. Value | DoD |
02/06/2023 | 1.0618 | 1.0542 | 0.0072 |
01/06/2023 | 1.0542 | 1.0411 | 0.0126 |
31/05/2023 | 1.0411 | 1.0453 | -0.0040 |
30/05/2023 | 1.0453 | 1..0591 | -0.0130 |
29/05/2023 | 1.0591 | 1.0598 | -0.0006 |
26/05/2023 | 1.0598 | 1.0508 | 0.0086 |
Appreciate any help.
Solved! Go to Solution.
Hi @Alezy
Please try
PR ULSD 10ppm MED CIF Cargoes (€/l) =
VAR CurrentDate =
MAX ( 'CALENDAR'[Date] )
RETURN
AVERAGEX (
TOPN (
1,
FILTER (
ALLSELECTED ( 'ULSD 10ppm CIF MED' ),
'ULSD 10ppm CIF MED'[Date] < CurrentDate
),
'ULSD 10ppm CIF MED'[Date]
),
'ULSD 10ppm CIF MED'[Litres EUR Prevat]
)
Hi @Alezy
Please try
PR ULSD 10ppm MED CIF Cargoes (€/l) =
VAR CurrentDate =
MAX ( 'CALENDAR'[Date] )
RETURN
AVERAGEX (
TOPN (
1,
FILTER (
ALLSELECTED ( 'ULSD 10ppm CIF MED' ),
'ULSD 10ppm CIF MED'[Date] < CurrentDate
),
'ULSD 10ppm CIF MED'[Date]
),
'ULSD 10ppm CIF MED'[Litres EUR Prevat]
)
That's exactly what I needed. Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |