Forum Discussion
Previous fiscal year calculation
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...
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"))
4 Replies
- amitchandak
Super User
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_0uACreating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441To 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.
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 ?
- amitchandak
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"))