topic Re: Previous fiscal year calculation in DAX Commands and Tips

Previous fiscal year calculation

Anonymous — Fri, 12 Feb 2021 06:47:53 GMT

Hi All,

below is my sample data,

I need to calculate gross margin for previous fiscal year (last column PY Gross margin) using dax measure.

April to next year march is my fiscal year period, ex 01-04-2019 to 31-03-2020.

I have a seperate date table, where i have my date column and extracted fiscal year from that column.

Gross Margin (second column in the image) is also calculated using dax measure in the sale Table.

I have tried using PREVIOUSYEAR and SAMEPERIODLASTYEAR dax functions, but it work fine only for calendar year.

Also, tried using [FY Year]=[FY YEAR]-1 and . None of them are working...

Thanks in advance...

Re: Previous fiscal year calculation

amitchandak — Fri, 12 Feb 2021 07:03:00 GMT

Anonymous , you can use time intelligence using date table

example

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Previous fiscal year calculation

Anonymous — Fri, 12 Feb 2021 08:12:01 GMT

Hi amitchandak ,

I have used the second measure you have mentioned.

Last YTD Sales = CALCULATE([Gross Margin],DATESYTD(dateadd(dates[Date],-1,Year),"3/31"))

below is my result,

I have data upto yesterday ie... 11-02-2021. So, its filtering fiscal year 2019 upto feb 11,2019 and showing gross margin sum up to that date in the third row (upto feb 11,2019 gross margin sum for fiscal year 2019 is -25,14,25157 ). But I need to show previous gross margin value as -26,45,056.48 for fiscal year 2020 row.

how can i prevent this filtering ?

Re: Previous fiscal year calculation

amitchandak — Fri, 12 Feb 2021 08:45:28 GMT

Anonymous ,

try

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

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

Re: Previous fiscal year calculation

Juben_Gladson — Mon, 25 Apr 2022 09:17:10 GMT

No I have the same issue but using this dax is not giving me the solution