Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |