EDITED: Dynamic Previous Row, excluding blanks

[BrianJ]

@Roboboboberts,

OK, When we start getting into the realm of discontinuous dates, as @Greg indicates, the DATEADD -1 construct is no longer going to work. Thus, we are going to have to switch over to the approach of using a variable to capture the current row context, which is fine.

I’m also going to assume that you are using @Melissa’s awesome extended date table. If not, you’ll see in a minute how much easier it will make this solution.

First, in terms of excluding blanks, I think the best way to do this will be via the filter pane. You can also do this in DAX, but as this post demonstrates, doing it via the filter pane typically is much, much easier both to implement and for someone else looking at your model to understand.

Now once we’ve got the extended date table set up and the blanks filtered out, the next step is getting the previous date ignoring weekends. I worked through that question in the post below, and @Sam.McKay also addressed it in a follow-up video:

DAX Row minus Previous Row

@Archer, This one ended up being pretty straightforward. We just needed to take our previous measure and add the following filter conditions in the PrevDate variable. [image] If we look at 5/23 - 5/26 below, we can see that this is working as expected. Monday picks up the Friday value, but in this case so does Tuesday, since Monday is a holiday (Memorial Day - I’m assuming US holidays, but if you plug in your holiday table column parameter into the extended date table, it will work regardle…

Retrive The Previous Value Taking Into Account All Weekends And Holidays - DAX Technique

So,the final step per your question is addressing extended weekends. If this four day weekend that you mention is just an isolated case, it can be addressed in the holiday table. If, however, you live in a wonderful world in which all weekends suddenly become four days, then this will be addressed in the extended date table in terms of how you define the IsWorking Day field in the extended date table code.

Your revised version of the measure discussed above will now look like this, and be completely dynamic as you adjust your weekend definition:

Previous Value No Weekends No Holidays =

VAR SelDate = SELECTEDVALUE( Dates[Date] ) 

VAR PrevDate = 
CALCULATE(
    MAX( Dates[Date] ),
    FILTER(
        ALL( Dates ),
        Dates[Date] < SelDate &&
        Dates[IsWorkingDay] = TRUE &&
        Dates[IsHoliday] = FALSE
    )
) 

RETURN
PrevDate

I think that should get you where you want to go. If not, please post a PBIX with some sample data and we can work through the specifics of you particular use case.

I hope this is helpful.

• Brian

P.S. In the extended date table, there is another field call IsBusiness Day, which is equivalent to

Dates[IsWorkingDay] = TRUE &&
Dates[IsHoliday] = FALSE

Thus, you could substitute those two lines in the measure with

Dates[IsBusinessDay] = TRUE

However, I sort of like the trransparency of the first construct, since it’s a bit more explict about how holidays are being handled.