Get the value for the previous non blank date
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.Date mismatches
Hello So...my date column in my date dimension is not working all of a sudden----I tried to ensure both are dates only but that is not working - when I use MONTHDATE (as below) it cannot get into date order. Surely this is silly .....any suggestions ?? I just need a simple solution on how to display month/year by the month name/yr and suddently my date table seems to be a string value
