Adding Non-Contiguous Dates

[StephenK]

Hello all,

I have a table like so:

Date	Item	QTY	CurrentDayStock	CurrentDayRestock	CurrentDayTotalStock	PrevDayStock	PrevDayRestock	NextDayStock	NextDayRestock
4/3/2020	Widget	ea	630	0	630	630	0
4/6/2020	Widget	ea	630	152	782		0	0

The PrevDay and NextDay columns are calculated columns that pull the previous/next day inventory using a variant of the following DAX measure:

NextDayStock =

VAR __Date = 'Fact'[Date]

VAR __Item = 'Fact'[Item]

VAR __Facility = 'Fact'[Facility]

VAR __Qty = 'Fact'[Qty]

VAR __Restock = 'Fact'[CurrentDayRestock]

VAR __Original = 'Fact'[CurrentDayStock]

VAR __Next =

MAXX(

FILTER(

'Fact',

[Date] = (__Date + 1) *1. &&

[Item] = __Item &&

[Facility] = __Facility &&

[Qty] = __Qty

),

[CurrentDayStock]

)

RETURN __Next

The idea is that I am pulling the NextDay/PrevDay stock into each row so that every row has the current stock, next day stock and previous day stock. All to get daily use and estimated days stock remaining.

I realized that there is an issue with this formula when the weekend hits. On a Friday, the row attempts to pull the next day stock, but it's looking for Saturday, which doesn't exist in the dataset. I need a way to account for this in the formula. Weekends should be excluded, so Friday pulls NextDayStock from Monday and vice versa. Anyone have any ideas on how I could accomplish this in my formula? 

[Pragati11]

HI @StephenK ,

I actually don't understand your DAX expression part where you have - [Date] = (__Date + 1) *1. &&

Check following article on how you can exclude weekends from your calculation and try to incorporate similar logic in your dax:

https://www.powerquery.training/networkdays/

https://stackoverflow.com/questions/40335558/exclude-weekends-in-powerbi-report

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[StephenK · Accepted solution]

I think I figured it out: 

VAR __Next_Date = CALCULATE(MIN('Fact'[Date]),FILTER('Fact','Fact'[Date]>__Date))

VAR __Next =

MAXX(

FILTER(

'Fact',

[Date] = __Next_Date &&

[Item] = __Item &&

[Facility] = __Facility &&

[Qty] = __Qty

),

[CurrentDayStock]

)

Since my fact table has non contiguous dates already excluding weekends, I realized I could just write an additional variable that returns the Min date after the current date or Max date before the current date to get my NextDate/PrevDate. Then I just plugged the variable into my formula to replace the (__Date + 1) piece.