Forum Discussion
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 table it has financial year. What would be the best way to get
Year to Date
Same period Last year
Variance against last year
An example of previous year variance is
Thanks
Chris
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 tableFinancialYear = 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
4 Replies
- Stachu
Community Champion
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 tableFinancialYear = 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
Thanks Stachu
All works great Apart from previous year to date.
I have
Debit YTD prev year = CALCULATE(sum('public nomdet'[debit]),SAMEPERIODLASTYEAR(DATESYTD('Calendar'[Date],"05/31")))Example
in 2018/2019 it only seems to do the year to date where there is a value in 2018/2019 rather than the months where are no values but values in 2017/2018