Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StephenK
Resolver I
Resolver I

Adding Non-Contiguous Dates

Hello all,

 

I have a table like so:

 

DateItemQTYCurrentDayStockCurrentDayRestockCurrentDayTotalStockPrevDayStockPrevDayRestockNextDayStockNextDayRestock
4/3/2020Widgetea63006306300  
4/6/2020Widgetea630152782 00 

 

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? 

 
1 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.

View solution in original post

2 REPLIES 2
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors