Yet another Burndown Challenge

[Anonymous]

Hello all,

I am trying to create a burndown chart.

I have two tables...

• tblWD
  This table contains all approved time entry at a Project/Worker/Date level of detail
• tblSOW
  This table contains SOW details, including Budgeted Hours, for each Project/Worker
  These tables have a relationship based on a concatenated key of [Project]_[Worker].

I am unable to calculate [hrsRemaining] = SUM( tblSOW[SOW_Bud_Hrs] ) - [hrsApproved_RT], where tblSOW[SOW_Bud_Hrs] should be [SOW_Bud_Hrs] per Worker given filter context and [hrsApproved_RT] is a measure to calculate the running total of tblWD[Approved_Hrs] for the same context.

Issues include, no records for dates without time entry.  For instance, no hours worked on 01/01.  However, the SOW started, so the [hrsRemaining] should be 2,000 = (1,000 + 1,000) - 0 (0 being [hrsApproved_RT] at the time).
Once started, my [hrsApproved_RT] measure carries through all dates, even those without activity, but [hrsRemaining] eludes me.

Below is an illustration of the results I am getting and the goal.  I have also included some sample data.

Any assistance would be appreciated.

Here is sample data.

tblWD

WD_Proj_Worker_Key	Project	WD_Worker	WD_Date	WD_Approved_Billable
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/02/2024	8.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/03/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/03/2024	9.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/04/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/04/2024	9.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/05/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/05/2024	8.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/08/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/08/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/09/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/09/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/10/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/10/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/11/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/11/2024	11.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/12/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/12/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/15/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/15/2024	9.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/16/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/16/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/17/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/17/2024	9.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/18/2024	6.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/18/2024	11.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/19/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/19/2024	11.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/22/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/22/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/23/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/23/2024	10.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/24/2024	9.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/25/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/26/2024	9.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/29/2024	10.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/30/2024	9.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/31/2024	8.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	02/01/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	02/01/2024	10.00
Project_XYZ_WorkerA	Project_XYZ	WorkerA	02/02/2024	8.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	02/02/2024	5.00

tblSOW

SOW_Proj_Worker_Key	Project	SOW_Worker	SOW_DtStart	SOW_DtEnd	SOW_Bud_Hrs
Project_XYZ_WorkerA	Project_XYZ	WorkerA	01/01/2024	06/30/2024	1,024.00
Project_XYZ_WorkerB	Project_XYZ	WorkerB	01/01/2024	06/30/2024	1,024.00

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Ensure that all measures are written.  I'll try to help with writing the Hrs remaining measure.

[Anonymous]

Hello @Ashish_Mathur,

Here is a small PBIX with my sample data and existing measures.
sampleBurndown.pbix 

Appreciate any assistance you can offer.  This is one of those challenges that doesn't seem like it should be as difficult as it is.

[Ashish_Mathur]

Hi,

I simply wrote this measure.  It seems to work fine.

Measure = [hrsBudgeted]-[hrsApproved_RT]

[Anonymous]

@Ashish_Mathur,
Your Measure did not resolve the problem.  The false-positive you had appeared to be due to a fluke with the abridged dataset I provided you.
My challenge is more difficult than my failing to subtract like your proposed solution suggested.

I added a third Worker, Worker C, to the file to help illustrate the continued issue.
Effectively, [hrsBudgeted]* is still not being limited to the context of the selection.

The challenge continues to be... for a given filter selection of Workers, what is the sum of their [SOW_Bud_Hrs] and report that value for all valid reporting days, even if they have 0 hours approved for the project.  The second scenario, reporting even when 0 approved hours are reported, occurs 01/02 and 01/23-01/26, when Worker B had no Approved hours.

The relationship between tblWD[WD_Proj_Worker_Key] and tblSOW[SOW_Proj_Worker_Key] is not supporting this sum of a singular value per Worker or presentation of the budget amount when no new approved time was reported.

*Current Measure...

hrsBudgeted =

    VAR Result =

            SUMX(

                tblSOW,

                tblSOW[SOW_Bud_Hrs]

            )

    RETURN

        Result

[Ashish_Mathur]

I can only interpret and work with the data that is shared.  If the data shared is not representative of the nuances of your actual data, then i obviously cannot forsee the scenario.

Share a small dataset to work with and show the expected result on that abridged dataset. 

[Anonymous]

I think I solved this. Required building a massively complicated M/PQ model, but results work and model makes senses. 

[v-yohua-msft]

Hi, @Anonymous 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[Anonymous]

@Ashish_Mathur,

As I mentioned, I updated the sample file with additional data.

 sampleBurndown.box