topic Adding Non-Contiguous Dates in DAX Commands and Tips

Adding Non-Contiguous Dates

StephenK — Mon, 06 Apr 2020 15:27:14 GMT

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?

Re: Adding Non-Contiguous Dates

Pragati11 — Tue, 07 Apr 2020 09:03:57 GMT

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

Re: Adding Non-Contiguous Dates

StephenK — Tue, 07 Apr 2020 12:29:28 GMT

I think I figured it out:

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