Forum Discussion

toball's avatar
toball
Regular Visitor
6 years ago
Solved

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!    Compan...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    toball
    Regular Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        toball
        Regular Visitor
        6 years ago

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