Running Total - Two dates - Received and Completed

[toball]

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)

Thanks in advance!

[Anonymous]

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).



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:

[toball]

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

[Anonymous · Accepted solution]

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)

[toball]

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