Forum Discussion

MAruna's avatar
MAruna
Icon for Helper I rankHelper I
9 months ago

Financial Year Data comparison with previous years

Hi Team, 

I need help on my new requirement.

I am using this Final measure :

Amount Display  =
VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])

-- Extract FY start and end
VAR FY_Start = VALUE(LEFT(SelectedFY,4))
VAR FY_End   = VALUE(RIGHT(SelectedFY,4))

-- Previous FY
VAR PrevFYText =
    FORMAT(FY_Start - 1, "0000") & "-" & FORMAT(FY_Start, "0000")

-- Slicer option
VAR Option = SELECTEDVALUE(YearOption[YearOption])

-- Selected divisor (Actuals, Thousands, Lakhs, Crores, Millions)
VAR DivValue =
    SELECTEDVALUE('Display Amount'[Divisor], 1)

-- Current Year
VAR CY =
    CALCULATE(
        SUM(MonthData[Amount]),
        MonthData[FinancialYear] = SelectedFY
    )

-- Previous Year
VAR PY =
    CALCULATE(
        SUM(MonthData[Amount]),
        MonthData[FinacialYear] = PrevFYText
    )

-- Main Logic
VAR Result =
    SWITCH(
        TRUE(),
        Option = "Current Year", CY,
        Option = "Previous Year", CY + PY,
        CY
    )

RETURN
    ROUND( Result / DivValue , 2 )

And

YearOption =
DATATABLE(
    "YearOption", STRING,
    {
        {"Current Year"},
        {"Previous Year"}
    }
)



This is working fine only but my requirement is 

when we select Financial Year like 2025-2026 and slected Previous Year slicer as well want to compare previous years data as well. Like 2025- 2026 and 2024-2025.

For this please find the below attached Image.

 



2) if do have any data for particular month can we set up a one message like " No data in this Month" .
Ex: Apr, May, Jun, July months data is there remaining months data is not there can we add one bar in that one data label visible "No data available in this months" Like this .

Can we acheive this?
Need Help

21 Replies

  • SavioFerraz's avatar
    SavioFerraz
    Icon for Super User rankSuper User
    9 months ago

    Hi MAruna,

     

    Yes, this is achievable.
    For previous year comparison, instead of adding CY + PY, return only PY when Previous Year is selected, and make sure the FY text (e.g. 2024-2025) is correctly derived from the selected FY. This will allow a clean comparison between 2025–2026 vs 2024–2025.

    For “No data available”, measures can’t add bars, but you can:

    Use a measure returning BLANK() for missing months

    Add a dynamic title or tooltip showing “No data available for this month”

    Helpful sources:

    CALCULATE & filter context: https://learn.microsoft.com/dax/calculate-function-dax

    SELECTEDVALUE in slicers: https://learn.microsoft.com/dax/selectedvalue-function-dax

    Handling BLANK() in visuals: https://learn.microsoft.com/power-bi/create-reports/desktop-handling-blank-values

    Microsoft Learn (recommended):

    Create measures using DAX in Power BI: https://learn.microsoft.com/training/modules/create-measures-dax-power-bi/

     

    Savio Ferraz | Microsoft Learning Consulting | Google Certified Trainer and Microsoft Certified Educator

    Did my answer help? Mark my post as a solution or like it if you found it useful.

     

     

    • MAruna's avatar
      MAruna
      Icon for Helper I rankHelper I
      9 months ago

      Hi SavioFerraz , Good Day!.

      If you have a time Please Share the sample pbx file that you have shared the snapchart 

  • MAruna's avatar
    MAruna
    Icon for Helper I rankHelper I
    9 months ago

    SavioFerraz , Thank you so much for your response.

    If you have a sample pbix please share.

    I am getting only single year , previous years comaprion in single graph i am not getting.

    Thanks in Advance.


  • krishnakanth240's avatar
    krishnakanth240
    Icon for Super User rankSuper User
    9 months ago

    Hi MAruna 

     

    Current Year Amount =
    VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])
    VAR DivValue = SELECTEDVALUE('Display Amount'[Divisor], 1)
    RETURN
    IF(
    NOT ISBLANK(SelectedFY),
    ROUND(
    CALCULATE(
    SUM(MonthData[Amount]),
    MonthData[FinancialYear] = SelectedFY
    ) / DivValue,
    2),
    BLANK()
    )

     

    Previous Year Amount =
    VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])
    VAR FY_Start = VALUE(LEFT(SelectedFY, 4))
    VAR PrevFYText = FORMAT(FY_Start - 1, "0000") & "-" & FORMAT(FY_Start, "0000")
    VAR DivValue = SELECTEDVALUE('Display Amount'[Divisor], 1)
    RETURN
    IF(
    NOT ISBLANK(SelectedFY),
    ROUND(
    CALCULATE(
    SUM(MonthData[Amount]),
    MonthData[FinancialYear] = PrevFYText
    ) / DivValue,
    2),
    BLANK()
    )

     

    Amount with No Data Message =
    VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])
    VAR CurrentMonth = SELECTEDVALUE(MonthData[MonthName])
    VAR CurrentYearAmount = [Current Year Amount]
    VAR PreviousYearAmount = [Previous Year Amount]

    -- Check if we have ANY data for this month
    VAR HasData = NOT (ISBLANK(CurrentYearAmount) && ISBLANK(PreviousYearAmount))

    RETURN
    IF(
    HasData,
    -- Show actual amount (for display purposes in tooltip)
    CurrentYearAmount,
    -- Show "No Data" placeholder (will be converted to 0 for chart)
    0 -- This creates a bar we'll label as "No Data"
    )

     

    Please give headup if it is working fine. Please let us know if any challenges. Thank You!

    • MAruna's avatar
      MAruna
      Icon for Helper I rankHelper I
      9 months ago

      Hi krishnakanth240 , Thanks for sharing the DAX..

      Current Year Amount =
      VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])
      VAR DivValue = SELECTEDVALUE('Display Amount'[Divisor], 1)
      RETURN
      IF(
      NOT ISBLANK(SelectedFY),
      ROUND(
      CALCULATE(
      SUM(MonthData[Amount]),
      MonthData[FinancialYear] = SelectedFY
      ) / DivValue,
      2),
      BLANK()
      )


      Previous Year Amount =
      VAR SelectedFY = SELECTEDVALUE(MonthData[FinancialYear])
      VAR FY_Start = VALUE(LEFT(SelectedFY, 4))
      VAR PrevFYText = FORMAT(FY_Start - 1, "0000") & "-" & FORMAT(FY_Start, "0000")
      VAR DivValue = SELECTEDVALUE('Display Amount'[Divisor], 1)
      RETURN
      IF(
      NOT ISBLANK(SelectedFY),
      ROUND(
      CALCULATE(
      SUM(MonthData[Amount]),
      MonthData[FinancialYear] = PrevFYText
      ) / DivValue,
      2),
      BLANK()
      )



      I have tried using this measures but when i select Previous year slicer column chart is not interacting..

      • krishnakanth240's avatar
        krishnakanth240
        Icon for Super User rankSuper User
        9 months ago

        Hi MAruna 

         

        Okay, Can you share some dummy data over excel file to work on it.

        Also, please share 1.Logic requirement 2.What output you are looking for from visual

        If you can explain with an example it would be helpful to understand.

  • MAruna's avatar
    MAruna
    Icon for Helper I rankHelper I
    9 months ago
    After did some changes in the measures coming like this.

    please help me on this issue.

    Current Year Amount =
    VAR SelectedFY =
        SELECTEDVALUE ( FY_Slicer[Financial Year] ) ---- Disconnected Table

    VAR OptionSelected =
        SELECTEDVALUE ( YearOption[YearOption], "Current Year" ) --- Disconnected Table

    VAR DivValue =
        SELECTEDVALUE ( 'Display Amount'[Divisor], 1 )  ---- Disconnected Table

    RETURN
    IF (
        OptionSelected IN { "Current Year", "Previous Year" }
            && NOT ISBLANK ( SelectedFY ),
        ROUND (
            CALCULATE (
                SUM ( Month Data[Amount] ),
                REMOVEFILTERS ( Month Data[Financial Year] ),
                Month Data[Financial year] = SelectedFY
            ) / DivValue,
            2
        )
    )


    Previous Year Amount =
    VAR SelectedFY =
        SELECTEDVALUE ( FY_Slicer[Financial year] ) ---- Disconnected Table

    VAR OptionSelected =
        SELECTEDVALUE ( YearOption[YearOption], "Current Year" ) --- Disconnected Table

    VAR FY_Start =
        VALUE ( LEFT ( SelectedFY, 4 ) )

    VAR PrevFYText =
        FORMAT ( FY_Start - 1, "0000" ) & "-"
            & FORMAT ( FY_Start, "0000" )

    VAR DivValue =
        SELECTEDVALUE ( 'Display Amount'[Divisor], 1 ) ----- Disconnected Table

    RETURN
    IF (
        OptionSelected = "Previous Year"
            && NOT ISBLANK ( SelectedFY ),
        ROUND (
            CALCULATE (
                SUM ( Month Data[Amount]),
                REMOVEFILTERS ( Month Data[Financial Year] ),
                Month Data[Financial Year] = PrevFYText
            ) / DivValue,
            2
        )
    )

     This is working fine but here in the legend selection based on the slicers section i need display like if we select 2025-2026 and selected previous year  display 
    2025-2026
    2024-2025 


    Thanks in Advance

    • V-yubandi-msft's avatar
      V-yubandi-msft
      Icon for Community Support rankCommunity Support
      9 months ago

      Hi MAruna ,

      The behaviour you are seeing is expected. Because the visual is using two separate measures, Power BI will always display the measure names (Current Year Amount and Previous Year Amount) in the legend.

      Power BI does not support dynamically renaming legend labels for measures.

      To display the legend as

      1. 2025–26
      2. 2024–25

      FYI:

       

      The recommended solution is to use one measure for the values and place the Financial Year column in the Legend field (as shown in the repro we tested). This allows the legend to automatically use the financial year values and react to the slicer selections.

       

      I’ve attached the PBIX file for your reference. Please review it and let me know if any changes or improvements are required.

       

      Hope this helps.....

      • MAruna's avatar
        MAruna
        Icon for Helper I rankHelper I
        8 months ago

        Hi V-yubandi-msft , 

        Thanks for the help. But when i select the Financial year slicer (Ex: 2025-2026) 
        Display 2025-2026 in the legend section
        When i select previous year slicer 
        Display 2024-2025 in the legend section only

        In single column chart.

        How to acheive this.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi MAruna ,

    Could you please confirm whether the issue has been resolved? If you need any further information or support, we’ll be happy to help.


    Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 months ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi MAruna ,

    Please let us know if your issue is resolved or if you need any further assistance.


    Thank you.

    • MAruna's avatar
      MAruna
      Icon for Helper I rankHelper I
      8 months ago

      Yet to be resolved still i am trying...

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Icon for Community Support rankCommunity Support
        8 months ago

        Thank you for the update. I understand and hope the investigation goes smoothly. If you find the root cause or a reliable workaround, please share it here, as it could help others with the same issue.

        Let me know if you need any more information from me.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi MAruna ,

    May I know if you have tried this from your side, and how it went? Is everything working fine, or do you need any additional information from me? Please let me know if anything else is required.

     

    Thank you.