Non Blank Pevious Day Values

Hi,
I am trying to get Today figures vs previous day figures.

For the previous day, i have created a measure as follows
Prev Day = Calculate([Today Figures], DateAdd(DateTable(date),-1,day))

However the issue is, the preceding day needn’t always have figures in it [owing to various reasons such as weekend, public holiday, no transaction etc]. In which case, how do i pick the last available non blank values before the current date?

For example;
today is 2nd Feb 2020 and the previous figures available are for 29th Jan 2020.
The prev day formula should pick up figures as on 29th Jan 2020.

@Jackson,

It should work if you add a second filter condition after your DATEADD statement to filter out the blank values on your measure:

Prev Day = Calculate([Today Figures], DateAdd(DateTable(date),-1,day),
FILTER( Data, [Today Figures] <> BLANK() ))

where Data is the appropriate table name from your model.

Hope this is helpful.

  • Brian
1 Like

Hi Brian,
Tried this. But seems to return a blank. :sweat_smile:

@Jackson,

Can you please post your PBIX file? If I can see your data and data model, I can be sure to provide you a more specific solution that will work.

Thanks.

  • Brian

Hi Jackson,

Try determining the last date with sales first, like below.

Previous day sales = 
VAR LastSales = 
    CALCULATE( MAX( Dates[Date] ),
        FILTER( ALL( Dates[Date] ),
            Dates[Date] < MAX(Dates[Date]) && 
            [Weekday Sales total] <>0
        )
    )
VAR Result = 
    CALCULATE( [Weekday Sales total],
        Dates[Date] = LastSales
    )
RETURN

IF( 
    LastSales <>BLANK() && [Weekday Sales total] <>0,
    Result,
    BLANK() 
)

And this is the result.

Here’s my sample file. I hope this was helpful.
eDNA - Non Blank Pevious Day Values.pbix (334.7 KB)

3 Likes

Wow, Many thanks Melissa. This works :grin: