Non Blank Pevious Day Values

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.


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:


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.


  • 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

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

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)


Wow, Many thanks Melissa. This works :grin: