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
Anonymous
Not applicable

Cumulative quantity per day and other fields

I need to calculate quantity per day in the last 90 days while excluding quantity and days during holiday weeks.

 

For example, given this sample data:

DayWeek NumberQuantity
11/1/20174410
11/30/20174830
12/10/20175050
1/15/201835
2/15/2018710

 

So, assuming that today is 2/19/18, I need to filter for everything on or after 11/21/17, and also for dates where Week Number < 47. Then I need to sum up the result (in this case, the result would be 15), and divide that by the number of days in the last 90 that aren't in a holiday week (using Mon-Sun weeks). In this case, that would be 50 days (Jan 1 to Feb 19). So 15/50 = 0.3/day. 

 

It's easy enough sum the quantity:

CALCULATE(SUM(Table1[Quantity]),Table1[Day] > (TODAY() - 90),Table1[Week Number] < 47)

 

But it's much harder to count the days when I can't use a MIN from the table or anything.

 

Any idea how to do this? 

4 REPLIES 4
parry2k
Super User
Super User

You need to include Calendar dimension in your model and flag which are holidays week and then from there you calculate number of days and execute your business logic.

 

There are many post on how to create calendar/date dimension in PowerBI and that is the key for your solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

I had hoped that since the logic was as simple as Week Number < 47, I might be able to do it as a formula rather than adding the complexity of an extra table.

I guess your week number is not always 47 as static value. As part of best practice, highly recommended to add date dimension on any time intelligence based reports.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

I've added a calendar table and can't figure out how to use it to get what I described in my original post. 

 

With the calendar table, I can more easily use Lookup to add a Holiday/Non-Holiday field that helps me sum the quanties for the proper dates. But I still can't figure out how to somehow add up the days elapsed from start to finish while excluding days categoried as "Holiday".

 

Any ideas?

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.