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 VA...
Need Help
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

      Hi krishnakanth240 , i am using above logic only. i have shared the bar chart as well. when i select Financial and previous year slicer i need to compare previous financial year as well.
      Please find the data.
      i could not able to upload the excel data.

       

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

        Hi MAruna 

         

        Okay, we can add the excel file into the gdrive, creating folder and upload it(Providing access to everyone) So that it can be worked on the dummy data. Thank you!

    • MAruna's avatar
      MAruna
      Icon for Helper I rankHelper I
      9 months ago
      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