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.
Good Afternoon,
I need help with a Power BI tasks that I've been struggling for a few days.
Let's suppose we are at 4/1/2017
My data looks like:
ID Order Company Starting Date End Date
1 Microsoft 1/1/2017 2/1/2017
2 Microsoft 2/1/2017 3/1/2017
3 Apple 2/1/2017 4/1/2017
4 Amazon 4/1/2017 4/1/2017
As you can see the column End Date shows when the order has been finished or if the order has not finished yet, shows the current date.
I need a measure that gives me the total remaining orders to be completed by date.
For example, if in day 1/1/2017, 1000 orders have started and 500 have finished, my remaining orders are 500.
In day 2/1/2017, 800 orders have started and 600 have finished, so my remaining orders are 500+800-600= 700
etc.
So my measure needs to calculate the accumulative value of the difference of the starting orders and the ending ones for each day (taking into account the previous value).
If I have not explained myself correct just let me know.
Thanks a lot.
So you want something that looks like:
Date - Remaining
1/1/17 - 500
2/1/17 - 700?
I assume you've got a date table, if not, make one and relate it to your data table. Make a couple of measures that count the number of rows in each of the start/end columns in your data table, then note that the count you want is equivalent to adding up all started and completed orders prior to the date, so in your date table you can make a calculated column that works out [allordersstarted]-[allorderscompleted], making sure that the filter context looks just at rows prior to today's date (probably needing to specify the correct relationship as one of them won't be active)
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |