Monthly Chart with Grand Total

[jfcarter66]

Hello.  I have a data table similar to what is below.  I have a monthly planned volume and monthly actual volume.  I want to create a bar graph that shows the planned and actual volume monthly but also include the YTD cumulative total.  Any ideas?

[v-pgoloju]

Hi @jfcarter66,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @jfcarter66,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @jfcarter66,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[jfcarter66]

Thank you.  Have another question.  How would I do this same task but for a fiscal year of April25-Mar 26?

[ajaybabuinturi]

Hi @jfcarter66, 
You need to create a Date Table/Fiscal Month column in your BI report and use the Fiscal Month.

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2026,12,31)), //(StartDate), (EndDate)
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date]),
    "Fiscal Year", IF(MONTH([Date]) >= 4, "FY" & YEAR([Date]) + 1, "FY" & YEAR([Date])),
    "Fiscal Month Number", MONTH(EOMONTH([Date], -3)),
    "Fiscal Month", FORMAT([Date], "MMM-YY")
)

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[jfcarter66]

My apologies on not following what you refer to above.  I create a column in my table with the DateTable calculation you provided and get an error.  I am probably overlooking something fairlly simple.   Thank you

[ajaybabuinturi]

Hi @jfcarter66, 
I hope you want to see your data by Fiscal Year. I'm suggesting you that you have to two columns in your data which are Fiscal Month Number and Fiscal Month. Make sure Fiscal Month is sorted by Fiscal Month Number. In that way you will see only Fical year data. 

I was just refering you Date Table as a example.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[v-pgoloju]

Hi @jfcarter66,

Thanks for reaching out to the Microsoft Fabric Forum Community. And also thanks to @ajaybabuinturi  for Prompt and helpful solution.

Please try creating a separate date table in your data model using the code provided. In Power BI Desktop, you can do this by going to Modeling > New Table and entering the code there. After creating the table, connect it with your existing table. This should help you get the results you need.

Best regards,
Prasanna Kumar

[ajaybabuinturi]

Hi @jfcarter66,

You can follow the below steps

1. Make sure Month column should be date format.

2. Create Measures for YTD Cumulative

YTD Planned = CALCULATE(SUM('YourTable'[Planned]), DATESYTD('YourTable'[Month]))
YTD Volume = CALCULATE(SUM('YourTable'[Volume]), DATESYTD('YourTable'[Month]))

3. Select the “Line and Clustered Column Chart”

Axis : Month

Column Y-axis : Planned, Volume

Line Y-axis : YTD Planned, YTD Volume

Please let me know if you have any questions.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.