cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
toball Frequent Visitor
Frequent Visitor

Running Total - Two dates - Received and Completed

Hi all,

 

Looking to replicate the second list in my attachment in Power BI running total or waterfall chart, I just cannot get my head around how to do this but know it must be simple! 

 

Company receives jobs with a set value each day. Columns in same list give received date and completion date (which is blank until completion date is entered)

 

Exampple.PNG

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Running Total - Two dates - Received and Completed

If we exclude those null entries directly in the Work Completed Sum, does that help?

Work Completed Sum = CALCULATE(
    [Work Received Sum],
    USERELATIONSHIP(JobList[Date Completed], Dates[Date]),
    NOT(ISBLANK(JobList[Date Completed]))
)

(I don't have my quick sample project, so i haven't tested out this code)


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

4 REPLIES 4
Ross73312 Super Contributor
Super Contributor

Re: Running Total - Two dates - Received and Completed

You do this with adding in a Date Table, which there are many guides around for.
Link your 'Job List' to the date table by both "Received Date" and "Completed Date" (one will need to be set to inactive).

Capture.PNG

Next we'll write 3 measures, one called "Work Received", "Work Completed", and "Incomplete Work".   Which ever date you set as being Active in your relationship, use that one first.  I'll assume its "Work Received".

Work Received Sum = VAR endDate = LASTDATE(Dates[Date])
VAR output = CALCULATE(
    SUM(JobList[Work Received]),
    ALL(Dates),
    Dates[Date] <= endDate
)
RETURN
output
Work Completed Sum = CALCULATE(
    [Work Received Sum],
    USERELATIONSHIP(JobList[Date Completed], Dates[Date])
)
Incomplete Work = [Work Received Sum] - [Work Completed Sum]


Now use the date table on your visuals along with the 3 measures and you can get:

Capture2.PNG

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


toball Frequent Visitor
Frequent Visitor

Re: Running Total - Two dates - Received and Completed

Hi Ross, 

 

Thanks so much, I have put this into my PowerBI sheet and I can see it is working. One problem - any items in the my list which do not yet have a completion date ('null' in my query table) are all summing as the first row with a blank heading. The result of this is that the Work Completed Sum starts out at a negative number equal to the amount of work not yet completed then gradually builds to zero. 

 

If I apply a filter, it removes the Value of the Work Received as well as the Work Completed. 

 

Sorry if this is a really quick fix that I am missing - I am new to PowerBI and am having fun learning but I am stuck again here. 

 

Cheers, Tobi

 

 

Ross73312 Super Contributor
Super Contributor

Re: Running Total - Two dates - Received and Completed

If we exclude those null entries directly in the Work Completed Sum, does that help?

Work Completed Sum = CALCULATE(
    [Work Received Sum],
    USERELATIONSHIP(JobList[Date Completed], Dates[Date]),
    NOT(ISBLANK(JobList[Date Completed]))
)

(I don't have my quick sample project, so i haven't tested out this code)


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

toball Frequent Visitor
Frequent Visitor

Re: Running Total - Two dates - Received and Completed

Awesome - works great now! Thanks so much Ross - appreciate the quick responses.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors