none
Project Burndown help RRS feed

  • Question

  • 

    I'm trying to make a burndown report and here is some sample data. I'm having trouble getting what I'm going for (I have a model in ssas, this is just data in pbi because it was easier to sort this way).

    As you can see through the versionWeek column, the initial plan was created and then was altered 7/28. 

    Here is what I'm trying to get. Everything is summed when it comes after the week start but before the next version of a certain week comes out. So looking at the sample data the 7/14 version week hoursPlanned area all summed until 7/28 when the new version of the last couple weeks shows up.

    The "Burndown" column is my goal here.

    Thanks in advance.

    Tuesday, August 6, 2019 6:47 PM

Answers

  • Hi jshinnenkamp,

    Thanks for your post.

    Here are the steps of table calculation for your reference.

    Step 1, create a calculated table for distinct values of the column "Week Start"

    CalculatedTable 1:=DISTINCT(SELECTCOLUMNS(Burndown,"WeekStart", Burndown[WeekStart]))


    Step 2,  get related records by joining table "CalculatedTable1" and table "Burndown" with filter conditions

    CalculatedTable 2:=SELECTCOLUMNS(FILTER(CROSSJOIN(Burndown,'CalculatedTable 1'),Burndown[WeekStart]>='CalculatedTable 1'[WeekStart] && Burndown[VersionWeek]<='CalculatedTable 1'[WeekStart]),"WeekStart",'CalculatedTable 1'[WeekStart],"WeekSmart2",Burndown[WeekStart],"VersionWeeK",Burndown[VersionWeek],"HoursPlanned",Burndown[HoursPlanned])

    Step 3, Add a calculated column "RANK_NUMBER" to table "CalculatedTable 2"

    RANK_NUMBER:=RANKX(FILTER('CalculatedTable 2','CalculatedTable 2'[WeekStart]=EARLIER('CalculatedTable 2'[WeekStart]) && 'CalculatedTable 2'[WeekSmart2]=EARLIER('CalculatedTable 2'[WeekSmart2])),'CalculatedTable 2'[VersionWeeK],,DESC,Dense)

    Step 4, Get the final result for the excel screenshot above.

    CalculatedTable 3:=SUMMARIZE(FILTER('CalculatedTable 2','CalculatedTable 2'[RANK_NUMBER]=1),'CalculatedTable 2'[WeekStart],"HoursPlanned",SUM('CalculatedTable 2'[HoursPlanned]))

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 7, 2019 7:53 AM

All replies

  • Hi jshinnenkamp,

    Thanks for your post.

    Here are the steps of table calculation for your reference.

    Step 1, create a calculated table for distinct values of the column "Week Start"

    CalculatedTable 1:=DISTINCT(SELECTCOLUMNS(Burndown,"WeekStart", Burndown[WeekStart]))


    Step 2,  get related records by joining table "CalculatedTable1" and table "Burndown" with filter conditions

    CalculatedTable 2:=SELECTCOLUMNS(FILTER(CROSSJOIN(Burndown,'CalculatedTable 1'),Burndown[WeekStart]>='CalculatedTable 1'[WeekStart] && Burndown[VersionWeek]<='CalculatedTable 1'[WeekStart]),"WeekStart",'CalculatedTable 1'[WeekStart],"WeekSmart2",Burndown[WeekStart],"VersionWeeK",Burndown[VersionWeek],"HoursPlanned",Burndown[HoursPlanned])

    Step 3, Add a calculated column "RANK_NUMBER" to table "CalculatedTable 2"

    RANK_NUMBER:=RANKX(FILTER('CalculatedTable 2','CalculatedTable 2'[WeekStart]=EARLIER('CalculatedTable 2'[WeekStart]) && 'CalculatedTable 2'[WeekSmart2]=EARLIER('CalculatedTable 2'[WeekSmart2])),'CalculatedTable 2'[VersionWeeK],,DESC,Dense)

    Step 4, Get the final result for the excel screenshot above.

    CalculatedTable 3:=SUMMARIZE(FILTER('CalculatedTable 2','CalculatedTable 2'[RANK_NUMBER]=1),'CalculatedTable 2'[WeekStart],"HoursPlanned",SUM('CalculatedTable 2'[HoursPlanned]))

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 7, 2019 7:53 AM
  • This did solve my initial question. I ran into a couple scenarios where this didn't work.

    Each recorded plan has a "PlanGroupId" and these plans can be altered- shortened or lengthened or whatever. When this happens it updates the version date which is what I use for "VersionWeek" 

    The way we are currently doing it, a plan that is altered to no longer include the current updates that version date for the entire plan resulting in the data no longer appearing in the table. Because we have the VersionWeek<=Week Start condition. 

    Curious if maybe there would be another way to do this to instead of ranking the weeks and only grabbing he active ones. We use the plan up to the date where it changed and then use the newest one.

    Here is an example of the issue:

    As you can see in the left table (raw data). There are 11 hours in the highlighted row, but in the right table (CalculatedTable2 from above) this entry doesn't exist because the week start comes before the version week. This results in my graph dropping to 0 for 8/4 when it should stay in 11.

    Here is a second scenario with an issue:

    A previous version of the plan contained a record for 8/11 (the highlighted entry on the left). However the plan was later altered to no longer include that entry. The issue comes from the the rank function only recognizes that 1 entry for the week resulting in a 1 even though it was altered. The box on the left are not being included in the right table for the same reason as above.

    any help would be great, thanks.



    Wednesday, August 14, 2019 8:46 PM
  • Hi jshinnenkamp,

    Since the current thread has been closed at that time, please close it as expected.

    Besides, the data that you provided is completely different from the one you just submitted. So you need to open another thread to submit your sample data with desired output, not reopen the closed thread. Due to that the data law has been changed, we need to reorganize the logic of DAX expression statements.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 8:01 AM