Hello, I have a fact table that resembles the below structure:
| Customer |
Order Number |
Start Date |
End Date |
Units |
Order Amount |
| Soccer Pro XYZ |
ABC123 |
1/1/2013 |
2/5/2013 |
50 |
5000 |
An order/contract might have a start and end date spanning between a few months.
What I’d like to be able to do is filter to a month, say Jan 2013, and see how many units fall within that month (could do the same for dollars by dividing units and order amount).
Ex. a pivot table
Month | Units Ordered | Order Amount
Jan-2013 | 500 | $2,245
Feb-2013 | 102 | $900
I was thinking a calendar table, and possibly a DAX formula to iterate over each order line would do the trick, but not having luck make it functionally work.
Any ideas? Thanks for any help and/or input!
Paul
Here’s exactly what you need for this.
It’s called the ‘events in progress’ pattern.
It’s very important to get the model and relationship set up correctly for this, as well as the DAX measures.
Everything is explained in the video. Should solve it.
Chrs
Hi Sam,
Thanks for the response. I believe this pattern gets me 75% of the way there, but where I’m stuck:
I need to split the order value by the number of billing days (end date - start date) that fall in each month. I believe the current DAX pattern would just allocate all of it in one of the months. So in my example, about 15% of the units (5 days in Feb) and order amount would considered produced or shipped in February.
This could be a granularity problem, and my data set is not set up for modeling, but wanted to see if DAX could solve for this.
Thanks,
Paul
Just checked on this and the patterns continues to allocate correctly even when in the monthly context see below.
You’ll see that the monthly time frame is reflecting exactly to the daily time frame.
Maybe I’m missing something?
Are you trying to instead of allocating the entire order or sales amount, allocate a portion of it across every day it is considered ‘open’.
If this is the case it shouldn’t be to difficult, you would just need calculate the different between the two dates and then use that to divide the total amount. This will allocate it out each day. Then you can use this calculation within the pattern.
Let me know if this works.
Hi Sam,
As usual, thanks for the quick and informative response.
You are correct at the end of your post - I would want to allocate the ‘open’ or ‘active’ amount (based on start and end date) within each month evenly across the days in that month. That’s the part of the DAX I’m having a tough time with.
Thanks again,
Paul
Ok I believe this is the best way to solve it right now.
Create another calculated column the works out the daily allocation amount. To do that you would need to work out the difference between end and start dates.
You should be able to easily work this logic into a calculated column.
From here, you just need to add up this column and use it within the pattern suggested above.
Can you give this a go. I believe it should work fine after this.
Thanks
