cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSS Regular Visitor
Regular Visitor

Acumulative Difference between dates

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.

1 REPLY 1
jthomson New Contributor
New Contributor

Re: Acumulative Difference between dates

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)

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 45 members 1,212 guests
Please welcome our newest community members: