create Measure that fetches value of Previous Fiscal Year-Month Based on Slicer Selection

[Sania-F]

HI All,

I need to write a DAX to create a measure that fetches the net% for the corresponding month in the previous fiscal year based on the selected value in the slicer.

My slicer has text type data in format Sep-fy23, oct-fy23 and so on. The data comes from period[month-year] table and starts from Sep-FY23.
I have a measure called net%(calculated at run time)

Now in my slicer if I select May-Fy24, then net% should display value for May-Fy23, 
if I select Apr-Fy24, then net% should display value for Apr-Fy23, 
So for any selected MMM-FYYY, my new dax should give me value for last FY.

I have written below dax which fetches the previous month-year in text format based on current slicer selection. Example if I select Jan-FY24, it return Jan-FY23. Now how can I leverage this formula to get Net% value returned for previous fiscal year

dax to fetch previous month-year based on current selection -

Previous_MMM_FYYY =

VAR SelectedMonthYearText = SELECTEDVALUE('Period'[Month-year])

VAR MonthText = LEFT(SelectedMonthYearText, 3) // Extracts the first 3 characters (month abbreviation)

VAR YearText = RIGHT(SelectedMonthYearText, 2) // Extracts the last 2 characters (year abbreviation)

VAR MonthNumber = MONTH(DATEVALUE(MonthText & "-01-2000")) // Converts month abbreviation to month number

VAR YearNumber = IF(VALUE(YearText) >= 50, 1900 + VALUE(YearText), 2000 + VALUE(YearText)) // Converts year abbreviation to full year

VAR SelectedDate = DATE(YearNumber, MonthNumber, 1) // Creates a date from the parsed month and year

VAR SelectedMonth = FORMAT(SelectedDate, "MMM-yyyy")

VAR ComparisonDate = DATE(YEAR(SelectedDate) - 1, MONTH(SelectedDate), 1)

VAR ComparisonMonth = FORMAT(ComparisonDate, "MMM-yyyy")

RETURN

   ComparisonMonth

I have also created a dax to return net% for previous month-year based on current slicer selection, but its returning nothing. Not sure whats going wrong

Net% Previous FY Month =

VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])

VAR SelectedMonth = LEFT(SelectedPeriod, FIND("-", SelectedPeriod) - 1)

VAR SelectedFY = RIGHT(SelectedPeriod, 2) + 2000  // Adjust if your fiscal year needs specific handling

VAR Previous_Year = SelectedFY - 1

VAR Previous_Month =

    SWITCH(

        SelectedMonth,

        "Jan", "Jan",

        "Feb", "Feb",

        "Mar", "Mar",

        "Apr", "Apr",

        "May", "May",

        "Jun", "Jun",

        "Jul", "Jul",

        "Aug", "Aug",

        "Sep", "Sep",

        "Oct", "Oct",

        "Nov", "Nov",

        "Dec", "Dec"

    )

VAR PreviousPeriod = Previous_Month & "-" & "FY" & RIGHT(Previous_Year, 2)

RETURN

--Previous_Year

--PreviousPeriod

CALCULATE(

    [Net%_measure],

    FILTER(period

        ,selectedvalue(period[Month-year])= PreviousPeriod

    )

)

Thankyou in advance.

[Sania-F · Accepted solution]

Hi All,

DAX that worked -

NetPercPreviousYear =

VAR SelectedMonthYear = SELECTEDVALUE(period[Month-year])

VAR SelectedYear = RIGHT(SelectedMonthYear, 2)

VAR SelectedMonth = LEFT(SelectedMonthYear, 3)

VAR Previous_Year = FORMAT(VALUE(SelectedYear) - 1, "0") //to remove decimals

VAR PreviousMonthYear = SelectedMonth & "-FY" & Previous_Year //to get date in format MMM-FYYY, Jun-FY23 when Jun-Fy24 is selected in slicer

RETURN

CALCULATE(

    [Net%_measure],

    FILTER(

        ALL(period),

        period[Month-year] = PreviousMonthYear --calculates net% till previous year

    )

)

[Rajdeep]

Then i can suggest you to assign the index to Period and get the next or previuous accordigly based on the 

l hope you can do it let m eknwo if you need help to do it. 🙏

[Rajdeep]

Hey Sania,

Try Prev DAX --> i hope it will help 

All the best 

[Sania-F]

HI @Rajdeep My Period[Month-year] is of text data type. So I cannot apply date functions on it directly.