Forum Discussion

FSP1303's avatar
FSP1303
Frequent Visitor
2 years ago
Solved

Cumulative Milestone Bar Chart does not display every week (if the Values are 0)

Hi,

I have a Bar Chart which sums the amount of forecasted and reached Milestones per week (CW).

Now especially at the beginning of a FY, when the forecasted and reached milestones are 0, the CW does not even appear. How can i edit the formula, so that it displays the Calendar Week but without any values?

 

Formula:

**bleep**. NP_Milestones =
CALCULATE(
    COUNT(Tasks[NP_Milestone]),
    FILTER(
        ALL(Calendar_Simple),
        Calendar_Simple[FiscalYear] = SELECTEDVALUE(Calendar_Simple[FiscalYear])
        && Calendar_Simple[Date] <= Max(Calendar_Simple[Date])
    )
)
 
For clarification: We mark the MilestoneTasks in the ProjectPlan by the column "NP_Milestone". So we get a Task with the Value "Milestone1". Then we count how many of those tasks there are in the portfolio.
 

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    Hi, FSP1303 

     

    Your current DAX formula is filtering for dates up to the maximum date found in your calender-sample table for the selected fiscal year. If no milestones are set or reached for a week, that week will not show any bars, and if the labels are set to auto, it might not display the week number either. 

     

    Make sure your calender-sample table has a row for every week, even those without milestones. then modify your DAX to the following

     

    NP_Milestones =
CALCULATE(
    IF(
        ISBLANK(COUNT(Tasks[NP_Milestone])), 
        0, 
        COUNT(Tasks[NP_Milestone])
    ),
    FILTER(
        ALL(Calendar_Simple),
        Calendar_Simple[FiscalYear] = SELECTEDVALUE(Calendar_Simple[FiscalYear])
        && Calendar_Simple[Date] <= MAX(Calendar_Simple[Date])
    )
)

     

    Also, you might want to adjust your viz to show values that are 0. 

     

    refer: Solved: bar chart suggestion to show zero values - Microsoft Fabric Community

    Show items with no data in Power BI - Power BI | Microsoft Learn

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi, FSP1303 

     

    Your current DAX formula is filtering for dates up to the maximum date found in your calender-sample table for the selected fiscal year. If no milestones are set or reached for a week, that week will not show any bars, and if the labels are set to auto, it might not display the week number either. 

     

    Make sure your calender-sample table has a row for every week, even those without milestones. then modify your DAX to the following

     

    NP_Milestones =
CALCULATE(
    IF(
        ISBLANK(COUNT(Tasks[NP_Milestone])), 
        0, 
        COUNT(Tasks[NP_Milestone])
    ),
    FILTER(
        ALL(Calendar_Simple),
        Calendar_Simple[FiscalYear] = SELECTEDVALUE(Calendar_Simple[FiscalYear])
        && Calendar_Simple[Date] <= MAX(Calendar_Simple[Date])
    )
)

     

    Also, you might want to adjust your viz to show values that are 0. 

     

    refer: Solved: bar chart suggestion to show zero values - Microsoft Fabric Community

    Show items with no data in Power BI - Power BI | Microsoft Learn

    • FSP1303's avatar
      FSP1303
      Frequent Visitor
      2 years ago

      Thanks that solved the issue! 🙂

  • FSP1303's avatar
    FSP1303
    Frequent Visitor
    2 years ago

    First Problem solved. Second Problem appeared:

     

    In CW 39 the Values differ, wether or not i have selected multiple FY to display. (As you can see in the filter above). So if I select only the FY 2023, the correct amount of 26 projects appear (but also some projects from CW52?!?). If I select FY2023 and 2024 to display, the CW 39 only shows empty values. 

    Does someone have an idea how to solve this issue?


    If further information is needed, please do not hesitate to contact me. 🙂