Non Blank Pevious Day Values

Jackson · February 4, 2020, 11:55am

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.

BrianJ · February 4, 2020, 12:52pm

@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

Jackson · February 4, 2020, 1:30pm

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

BrianJ · February 4, 2020, 1:34pm

@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

Melissa · February 4, 2020, 5:17pm

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)

Jackson · February 5, 2020, 11:23am

Wow, Many thanks Melissa. This works

bgood · April 2, 2020, 8:44pm

Hi @Melissa, this was a great solution to a problem I had and it solved it perfectly.

My situation is a little different though and on some days the ‘price’ I have captured for a particular commodity on aparticular day could be exactly the same as the previous date. What i’d love to be able to do is say if the previous day’s price is the same as the current max date price, then skip and move to the next previous days price.

Do you think this would be possible?
Regards,
Bronwyn

Melissa · April 2, 2020, 8:58pm

Hi @bgood,

Sure, first you probably need to calculate a [Last Day Sales] (or current max date price) but when you’ve done that you can add it to the FILTER condition.

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

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

bgood · April 3, 2020, 1:25am

Thanks @Melissa, i’m not sure this satisfy’s my strange requirement. Your solution works where there is a blank cell, but not where the latest price and last price are the same.

This is what i am trying to achieve:

Melissa · April 3, 2020, 11:15am

@EnterpriseDNA,
This is a new question, could you please split this thread at post number 7.
Subject: Compare last two previous price changes
Thank you!

.

@bgood,

See if this will do the trick for you. First measure is basically the same I only had to modify it to illustrate your request.

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

IF( LastSales <>BLANK() && [Weekly Sales total] <>0, ResultLastSales, BLANK() )

.
Next the measure to step back to the price before that.

Previous day sales <> to last day = 
VAR LastSales = 
    CALCULATE( MAX( Dates[Date] ),
        FILTER( ALL( Dates[Date] ),
            Dates[Date] < MAX(Dates[Date]) && 
            [Weekly Sales total] <>0 
        )
    )
VAR ResultLastSales = 
    CALCULATE( [Weekly Sales total],
        Dates[Date] = LastSales
    )
VAR PreviousDate =
    CALCULATE( MAX( Dates[Date] ),
        FILTER( ALL( Dates[Date] ),
            Dates[Date] < MAX(Dates[Date])  && 
            [Weekly Sales total] <>0 &&
            [Weekly Sales total] <> ResultLastSales
        )
    )
VAR ResultPreviousSales = 
    CALCULATE( [Weekly Sales total],
        Dates[Date] = PreviousDate
    )
RETURN 

IF( PreviousDate <>BLANK() && [Weekly Sales total] <>0, ResultPreviousSales, BLANK() )

.
and finally the change between these previous prices

Change = 
[Previous day sales v2] - COALESCE( [Previous day sales <> to last day], [Previous day sales v2] )

Here is my sample file. I hope this is helpful.
eDNA - Non Blank Pevious Day Values.pbix (337.0 KB)

bgood · April 5, 2020, 9:54pm

Wow! This worked @Melissa, that’s brilliant Thanks you so much!