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  thank you so much for your reply.
Your solution works very well with Fiscal Year and other dimensions, but when I add month and week  to the visual the number is repeated, it does not break it down by those date attributibutes.

Nabha-Ahmed's avatar
Nabha-Ahmed
Icon for Super User rankSuper User
9 months ago

Try this code 

Dax measure:

LastYearSales :=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR LastYearStart =
DATE ( YEAR ( MaxDate ) - 1, 1, 1 )
VAR LastYearEnd =
DATE ( YEAR ( MaxDate ) - 1, 12, 31 )
RETURN
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= LastYearStart
&& 'Date'[Date] <= LastYearEnd
)
)

  • 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
      )
      )