topic Re: Fiscal Year, Calender Year calculation in DAX Commands and Tips

Fiscal Year, Calender Year calculation

KH_Mike — Thu, 03 Jun 2021 05:42:20 GMT

Hi All,

I have a set of data like below. Right now, all the calculation is based on Fiscal Year like below Year on Year Comparsion. Now, I need to add the [Year] slicer as well. May I know if there is any way to shift all the calculation from Fiscal Year to Calendar Year?

One of my DAX is as follow.

YOY=
VAR LY=SELECTEDVALUE(Table[Fiscal Year])-1
VAR TY=SELECTEDVALUE(Table[Fiscal Year])
VAR _Month = SUMMARIZE (FILTER (Table,Table[Fiscal Year] = TY),Table[Month])

Return

sum(Table[Volume])
-
CALCULATE(sum(Table[Volume]),Table[Fiscal Year]=LY,Table[Month] in _Month)

Fiscal Year	Year	Month	Volume
2019	2020	Jan	1
2019	2020	Feb	2
2019	2020	Mar	3
2020	2020	Apr	4
2020	2020	May	5
2020	2020	Jun	6
2020	2020	Jul	7
2020	2020	Aug	8
2020	2020	Sep	9
2020	2020	Oct	10
2020	2020	Nov	11
2020	2020	Dec	12
2020	2021	Jan	13
2020	2021	Feb	14
2020	2021	Mar	15

Re: Fiscal Year, Calender Year calculation

amitchandak — Thu, 03 Jun 2021 06:20:27 GMT

KH_Mike , if you have date with help from date table

or create with help from year month and day

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Re: Fiscal Year, Calender Year calculation

KH_Mike — Thu, 03 Jun 2021 07:28:32 GMT

Hi amitchandak

Thank you for your reply. Except creating a date table, any chance to acheive it by pure DAX formula? Thank you.

Re: Fiscal Year, Calender Year calculation

KH_Mike — Mon, 07 Jun 2021 06:16:45 GMT

Anyone can help me?