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!