Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Custom Financial Year

Hi,   Our financial year runs from Jun 1st to May 31st is there an 'easy' way to create a new column in the calendar table which has the relvent year against the date.     In our nominal detail ...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    you can specify custom financial year in the calculation
    https://docs.microsoft.com/en-us/dax/datesytd-function-dax
    you will need custom column in your calendar table

    FinancialYear = 
VAR __month = MONTH('Calendar'[Date])
VAR __year = YEAR('Calendar'[Date])
RETURN
IF(__month<6, __year-1 & "/" &__year, __year & "/" & __year+1)

    SAMEPERIODLASTYEAR will work fine whenever you select year from this

    as for YTD, you can use DATESYTD, e.g.

    YTD = 
CALCULATE(
    SUM('public nomhead'[nominal]), 
    DATESYTD(Calendar[Date], "31-05")
)

    for PY YTD you can just use SAMEPERIODLASTYEAR within calculate that uses the [YTD] measure

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I set the direction in the relatio bship betwen the calendar and fact table as one way as per other forum post and it seems to work.

 

Strange... would be useful to know why