Get the value for the previous non blank date

[Alezy]

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.

[tamerj1 · Accepted 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]
    )

[Alezy]

That's exactly what I needed. Thanks a lot!