Forum Discussion

sabd80's avatar
sabd80
Icon for Helper IV rankHelper IV
9 months ago
Solved

DAX Last Year based on custom date dimension

Hi, I have a customer date table for finacnial date, I want to calculate the measures for last year. Because I have a customer date table, I can't use the time intelligence functions. I would lik...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    9 months ago

    Hi sabd80,
    Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.


    I am also including .pbix file for your better understanding, please have a look into it:

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Fabric Community Forum.

sabd80's avatar
sabd80
Icon for Helper IV rankHelper IV
9 months ago

Nabha-Ahmed the above code did not work.
I have replaced the code of LastYearStart and LastYearEnd  to point to the fiscal year start date and fiscal year end date, but it did not work

sabd80's avatar
sabd80
Icon for Helper IV rankHelper IV
9 months ago

Nabha-Ahmed unfortunitally it does not work, it repeats the yearly figure on month and week level.

This my DAX:

Testing2 Measture LY =

VAR currentYear= MAX ( 'Date'[Fiscal Year] )
VAR LastYearStart = CALCULATE(MAX('Date'[Fiscal Year Start Date]),  FILTER(ALL('Date'[Fiscal Year]),'Date'[Fiscal Year] =  currentYear-1))
VAR LastYearEnd = CALCULATE(MAX('Date'[Fiscal Year End Date]), FILTER(ALL('Date'),'Date'[Fiscal Year] =  currentYear-1))

RETURN

CALCULATE (
        [Measture],
    FILTER (
        ALL('Date') ,
            'Date'[Calendar Date] >= LastYearStart &&
            'Date'[Calendar Date] <= LastYearEnd
)
)