Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a set of historic data about all the orders we have received and shipped.
I know from this data that an order has been a 'backorder' between a startdate and an enddate.
What I want to do is show the backorder value of the orders at a certain point in time. I am guessing that I need to have a unique table with dates (1 row for every day), and link this with the historic backorder data... But how?
For example
Order number | Start date backorder | End date backorder | Order value |
A | 19-Oct | 25-Oct | €10 |
B | 22-Oct | 27-Oct | €20 |
C | 24-Oct | 30-Oct | €15 |
If I were to slice to see the backorder status for 20-Oct I would see a value of €10
If I were to slice to see the backorder status for 23-Oct I would see a value of €30 (order A + B)
And so on...
Thank you in advance for your help!
Solved! Go to Solution.
Hi,
1. Add a calendar table as slicer:
Table 2 = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))
2. Add below measure:
Measure = CALCULATE(SUM('Table'[Order value]),FILTER('Table',[Start date backorder]<=MAX('Table 2'[Date])&&[End date backorder]>=MIN('Table 2'[Date])))
Hi,
1. Add a calendar table as slicer:
Table 2 = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))
2. Add below measure:
Measure = CALCULATE(SUM('Table'[Order value]),FILTER('Table',[Start date backorder]<=MAX('Table 2'[Date])&&[End date backorder]>=MIN('Table 2'[Date])))
User | Count |
---|---|
87 | |
72 | |
69 | |
64 | |
55 |
User | Count |
---|---|
99 | |
91 | |
80 | |
74 | |
64 |