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

[FSP1303]

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.

[FSP1303]

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. 🙂

[rubayatyasmin · Accepted solution]

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]

Thanks that solved the issue! 🙂