Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX HELP

I want to calculate current Fiscal Year End Date (April - March)

Suppose for Today, Fiscal year end date should be 31-03-2021
Need Help
  • Vvelarde's avatar
    Vvelarde
    6 years ago

    Anonymous 

     

    Hi, try with this calculated column:

    CurrentEndFiscalYear =
IF (
    MONTH ( 'Table'[Date] ) <= 3;
    DATE ( YEAR ( 'Table'[Date] ); 03; 31 );
    DATE ( YEAR ( 'Table'[Date] ) + 1; 03; 31 )
)

     

    Regards

     

    Victor

     

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Not a pro in dax. Can you please help on this. I tried formulas , but getting wrong data.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        I am not clear on what you want. Are you saying that you want to have a "total ytd" measure that runs from April 1st, 2019 to March 31st, 2020 for example? If so, perhaps something like:

         

        Measure =
  VAR __Date = MAX([Date])
  VAR __Year = YEAR(__Date)
  VAR __FiscalEndMonth = 3
  VAR __FiscalEndDay = 31
  VAR __FiscalBeginMonth = 4
  VAR __FiscalBeginDay = 1
  VAR __FiscalBegin = DATE(__Year - 1,__FiscalBeginMonth,__FiscalBeginDay)
  VAR __FiscalEnd = DATE(__Year, __FiscalEndMonth,__FiscalEndDay)
RETURN
  SUMX(FILTER('Table',[Date] >= __FiscalBegin && [Date] <= __FiscalEnd),[Column])