Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Day | Week Number | Quantity |
11/1/2017 | 44 | 10 |
11/30/2017 | 48 | 30 |
12/10/2017 | 50 | 50 |
1/15/2018 | 3 | 5 |
2/15/2018 | 7 | 10 |
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?
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.
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.
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?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |