Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
FSP1303
Frequent Visitor

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.
 

FSP1303_0-1700640987202.png

3 REPLIES 3
FSP1303
Frequent Visitor

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

FSP1303_0-1700647527051.pngFSP1303_1-1700647624964.png

 

rubayatyasmin
Super User
Super User

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


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Thanks that solved the issue! 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.