powerbidaxpowerbi-desktopburndowncharts

SUMMARIZE or SUM values based on Date fields in another table

I am trying to do a burndown graph on PowerBI.

My data is a list of tasks. Each task has a numerical value (EFFORT) assigned to it and there is a total amount of effort for any given month (sum of all EFFORT). As tasks as set to DONE, the ongoing effort should be deducted from a running total and that value used to plot a graph. I have 3 columns

enter image description here

I would like to have measure to calculate EFFORT REMAINING for each date, i.e.

EFFORT REMAINING = TOTAL EFFORT - (EFFORT WHEN TASKS ARE DONE FOR EACH DAY)

For example,

enter image description here

I did get the consecutive dates displaying:

Burndown = CALENDAR(DATE(2022,7,1),DATE(2022,7,31))

enter image description here

and also the total effort (starting value)

TOTAL EFFORT = SUM(Issues[EFFORT])

Now for each date in table, I need to minus the accumulating total of EFFORT when the status is set to DONE

EFFORT REMAINING = Burndown[TOTAL EFFORT]-SUM(Issues[EFFORT]="DONE" ....

Im stuck after this last point. Can anyone help, please?

Solution

  • you are so close to the answer ). Convert SUM(Issues[EFFORT]="DONE" to:

    CALCULATE(
    SUM(Issues[EFFORT])
    , SUM(Issues[Status]="DONE"
)

    Have a nice day.