Forum Discussion

jfcarter66's avatar
jfcarter66
Regular Visitor
1 year ago
Solved

Monthly Chart with Grand Total

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 al...
Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
    ajaybabuinturi
    1 year ago

    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.

jfcarter66's avatar
jfcarter66
Regular Visitor
1 year ago

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

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      jfcarter66
      Regular Visitor
      1 year ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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