Forum Discussion

SoSoDef276's avatar
SoSoDef276
New Member
4 years ago
Solved

Calendar Auto Logic

Hello, 

 

I created a Fiscal Year Date Table using the below ; 

 

Fiscal Year Table = CALENDARAUTO(3)
 
and captured the Fiscal Year with this ; 
 
Fiscal Year =

VAR CurrYear = RIGHT(YEAR('Fiscal year Table'[Date]),2)
VAR LastYear = RIGHT(YEAR('Fiscal year Table'[Date])-1,2)
VAR NewYear = RIGHT(YEAR('Fiscal year Table'[Date])+1,2)
VAR FiscalYear =

SWITCH(
TRUE (),
MONTH('Fiscal year Table'[Date]) >= 4,
CurrYear & "-" & NewYear,
LastYear & "-" & CurrYear
)
RETURN

FiscalYear
 
As of now it will only capture up to March 31, 2023. Will this self-update when I start inputting data for the next fiscal ?
 

 

Hoping I don't need to keep revising. If so, can someone recommend a fix?

 

 

 

Tips and Tricks
  • speedramps's avatar
    speedramps
    4 years ago

    Hi SoSoDef276

     

    You are using a bad practice method ğŸ˜®

     

    The best practice is to create a Calendar table in Power Query with a list of dates and a Fiscal Year column.

    The create adate relationship to your fact tabales.

    You can then slice, total or compare by Fiscal Year ğŸ˜€ğŸ˜€ğŸ˜€

     

    If you are ever using data logic ... always use calanadar table 
    dont start adding lots of obscure code like your example.  ğŸ˜€

     

    Click here to watch video 

     

    Please clcik thumbs up ad accept as solution

     

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    Hi SoSoDef276

     

    You are using a bad practice method ğŸ˜®

     

    The best practice is to create a Calendar table in Power Query with a list of dates and a Fiscal Year column.

    The create adate relationship to your fact tabales.

    You can then slice, total or compare by Fiscal Year ğŸ˜€ğŸ˜€ğŸ˜€

     

    If you are ever using data logic ... always use calanadar table 
    dont start adding lots of obscure code like your example.  ğŸ˜€

     

    Click here to watch video 

     

    Please clcik thumbs up ad accept as solution