Forum Discussion

LondiweNkosi's avatar
LondiweNkosi
Frequent Visitor
3 months ago
Solved

Power BI Year Over Year analysis using the matrix visual

Hi Community,
I have 2 slicers, one for Years and one for Months. They both allow multiple selection. I have also created the matrix visual of the data. and In the rows there's ustomer names, in the columns I have Year and Month represented in the format (May-2022). The format changes based on the Year and month selected on the slicer. I have the Date table created as well. The data is in the format below.

I need help calculating the % change based on the selection made on the slicers. Your assistance would appreciated.

Need Help
  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    3 months ago

    Hi LondiweNkosi,
    Thanks for reaching fabric community, You can perform below steps to calculate %chnage by selecting the slicers.

    1) Relationship should be like between your sales and date table.

     

    2) Create measures

    - GP = SUM ( SalesData[GP] )

    - GP Previous Year =
    CALCULATE (
        [GP],
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
    - GP YoY % =
    DIVIDE ( [GP] - [GP Previous Year], [GP Previous Year] )

     

    3) Matrix visual format should be like this

     

    Just want to conform you need YOY % chnage is that correct?
    Please consider as an accepted solution if helps or give some kudos.

    sample table I created

    SalesData = 
DATATABLE (
    "Customer", STRING,
    "Date", DATETIME,
    "GP", INTEGER,
    {
        { "DTX", "2023-03-01", 1825000 },
        { "DTX", "2024-03-01", 1745623 },

        { "Ftech", "2023-03-01", 2325460 },
        { "Ftech", "2024-03-01", 1014587 },

        { "Clicks", "2023-03-01", 985625 },
        { "Clicks", "2024-03-01", 1003265 },

        { "Joe Soap", "2023-03-01", 114522 },
        { "Joe Soap", "2024-03-01", 256341 }
    }
)
    Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "MMM yyyy" )
)

     




10 Replies

  • LondiweNkosi's avatar
    LondiweNkosi
    Frequent Visitor
    3 months ago

    wardy912   Lodha_Jaydeep Both methods are working perfectly for calculating the %change, However I only want one total column that shows the difference between the 2 years.

    The picture above shows multiple columns but I only need one total column showing the difference between the years. The picture below shows the example of how the data must be visualised.

     

    • wardy912's avatar
      wardy912
      Icon for Super User rankSuper User
      3 months ago

      Hi LondiweNkosi 

       

       Simply remove totals from the visual using the format pane.

      --------------------------------

      I hope this helps, please give kudos and mark as solved if it does!

       

      Connect with me on LinkedIn.

      Subscribe to my YouTube channel for Fabric/Power Platform related content!

       

      • LondiweNkosi's avatar
        LondiweNkosi
        Frequent Visitor
        3 months ago

        Hi wardy912 The Total column is the one that should appear showing the overall total between the 2 years (including GP total, GP Difference total and GP percentage total). what I do not need is the GP difference and GP Percentage in between the Month Year Columns. I hope this clarifies the problem I am currently faced with.

  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    Icon for Solution Sage rankSolution Sage
    3 months ago

    Hi LondiweNkosi,
    Thanks for reaching fabric community, You can perform below steps to calculate %chnage by selecting the slicers.

    1) Relationship should be like between your sales and date table.

     

    2) Create measures

    - GP = SUM ( SalesData[GP] )

    - GP Previous Year =
    CALCULATE (
        [GP],
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
    - GP YoY % =
    DIVIDE ( [GP] - [GP Previous Year], [GP Previous Year] )

     

    3) Matrix visual format should be like this

     

    Just want to conform you need YOY % chnage is that correct?
    Please consider as an accepted solution if helps or give some kudos.

    sample table I created

    SalesData = 
DATATABLE (
    "Customer", STRING,
    "Date", DATETIME,
    "GP", INTEGER,
    {
        { "DTX", "2023-03-01", 1825000 },
        { "DTX", "2024-03-01", 1745623 },

        { "Ftech", "2023-03-01", 2325460 },
        { "Ftech", "2024-03-01", 1014587 },

        { "Clicks", "2023-03-01", 985625 },
        { "Clicks", "2024-03-01", 1003265 },

        { "Joe Soap", "2023-03-01", 114522 },
        { "Joe Soap", "2024-03-01", 256341 }
    }
)
    Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "MMM yyyy" )
)

     




  • wardy912's avatar
    wardy912
    Icon for Super User rankSuper User
    3 months ago

    Hi LondiweNkosi 

     

     This seems like a good use case for visual calculations. It will be the quickest and easiest option for you.

     

    Click the 3 dots in the top right of the matrix visual, select 'New visual calculation', 'Custom'

     

     

    In the formula bar, add:

    DIVIDE (column2, column1)

     

    Click accept and go back to the report.

    Open the format pane/properties and expand 'data format'

    Select your new visual calculation column and format as percentage

     

    --------------------------------

    I hope this helps, please give kudos and mark as solved if it does!

     

    Connect with me on LinkedIn.

    Subscribe to my YouTube channel for Fabric/Power Platform related content!

     

     

  • v-shchada-msft's avatar
    v-shchada-msft
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi LondiweNkosi  
    Thank you for reaching out to the Microsoft Fabric Community Forum.

    Just checking in, were you able to resolve the issue? If not please feel free to share an update and we will be happy to assist further.
    Your feedback will also help others facing similar challenges.

    Thank you.