none
Project Burndown cont. RRS feed

  • Question

  • I previously asked this quesion which didnt quite solve my issue:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/842f501a-9062-48dd-97de-26f8a3586ac9/project-burndown-help?forum=sqlanalysisservices

    Like I said in that thread, the goal is the total the hours that have been planned and have them drop off the graph as the week goes by. Along with altering that plan if the plan is altered based on the week in which is was altered. I'm going to give three examples of sample data and expected result (Shown in pbi and excel, but I want the solution in my ssas model - just easier to display data this way).

    (The right images will be the basis of my chart -Week Start being the x-axis and Burndown being the values).

    Example 1: 

    The first two weeks are calculated with the 10's until the plan is altered on the week of the 28th. 13's then replace it.

    Example 2:

    Left image 4th column is the planGroup. As you can see this example has two of them. Both created on 7/14, but the second one was altered on the 28th and again 11th.

    Example 3:

    This one is a bit different because the planGroup ending in 8096 has those 0 entries on the 11th. This is because the plan was altered to no longer include the current week (which is the 11th). At the same time 2 new planGroups were made for future weeks.

    I'm thinking we will need to due something with the PlanGroups in order for this to work properly, but I guess I'm not sure.

    Any help would be appreciated. Thanks.

    Thursday, August 15, 2019 8:38 PM

Answers

  • Hi jshinnenkamp,

    Thanks for your reply.

    Here are answers for your doubts.

    >>For the 2nd one, it is working as expected aside from the week of 8/4 where it is returning a 0 instead of 22. 

    Based on the screenshot of the example two, the values of the calculated column "RANK_NUMBER" are not correct. Therefore, the DAX expression of the that column has some problem. So you need to check it based on my solution in another thread.

    >>The third example is also giving me a small issue, the week of 8/11 im returning 75 instead of expected 50. The plan group ending in 8096 previously contained the week of 8/11, but during the week it dropped that week. However, the resulting table still recognizes it as the only entry for that week giving it a rank of 1 still.

    The third example is also the same issue as the second one. If you couldn't find out what reason leading to such issue. Please show us your DAX expression of the calculated column.

    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.

    Tuesday, August 20, 2019 7:10 AM

All replies

  • Hi jshinnenkamp,

    Based on my test about the three examples above, the original solution is available to get expected output. Have you missed anything else?

    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.

    Friday, August 16, 2019 5:40 AM
  • Example 1 is working fine, but the other 2 are giving me trouble.

    For the 2nd one, it is working as expected aside from the week of 8/4 where it is returning a 0 instead of 22. 

    Left table is raw data, right is the calculated table 2 from your solution. As you can see in the right table all the records with 8/4 as Week Start don't have a rank of 1 so they aren't being included in the final table. I'm not sure if this is because we have the condition of VersionWeek<=WeekStart when creating this table? 

    The third example is also giving me a small issue, the week of 8/11 im returning 75 instead of expected 50. The plan group ending in 8096 previously contained the week of 8/11, but during the week it dropped that week. However, the resulting table still recognizes it as the only entry for that week giving it a rank of 1 still.

    Highlighted on the left you see the 8/11 record. Right below it the chunk where the plan was shortened and you no longer see the week of 8/11 included. However in the right table you see the highlighted entry of 8/11 with a rank of 1. But we need that to no longer be included.

    Friday, August 16, 2019 12:17 PM
  • Hi jshinnenkamp,

    Thanks for your reply.

    Here are answers for your doubts.

    >>For the 2nd one, it is working as expected aside from the week of 8/4 where it is returning a 0 instead of 22. 

    Based on the screenshot of the example two, the values of the calculated column "RANK_NUMBER" are not correct. Therefore, the DAX expression of the that column has some problem. So you need to check it based on my solution in another thread.

    >>The third example is also giving me a small issue, the week of 8/11 im returning 75 instead of expected 50. The plan group ending in 8096 previously contained the week of 8/11, but during the week it dropped that week. However, the resulting table still recognizes it as the only entry for that week giving it a rank of 1 still.

    The third example is also the same issue as the second one. If you couldn't find out what reason leading to such issue. Please show us your DAX expression of the calculated column.

    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.

    Tuesday, August 20, 2019 7:10 AM