Thank you! Because of you, I noticed and started to like my username
About the problem:
I analyzed the article and wrote the formula I needed. Sadly, I still have one more question; is there a way to automatically reference the last cell that contains a value? How do I make a formula that ignores the blanks?
For example, please see the below formula; it references the last cell with a value excluding weekends. I want to make this formula automatic, so, for instance, if weekends suddenly are four days instead of two, the procedure would not need to be adjusted.
Turnover Previous Date =
VAR PreviousMeasure = CALCULATE([Turnover 2],DATEADD(Calendar_Perfect[Date],-1,DAY))
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.
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:
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 =
MAX( Dates[Date] ),
ALL( Dates ),
Dates[Date] < SelDate &&
Dates[IsWorkingDay] = TRUE &&
Dates[IsHoliday] = FALSE
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.
P.S. In the extended date table, there is another field call IsBusiness Day, which is equivalent to