Forum Discussion
custom YTD calculation help
hi,
I want to calculate YTD, Previous YTD sales total calculations. But here in my date table, Fiscal year start from 01/05/2021 for year 2021 and for each year it is different. I have two columns for 'FiscalYearStartDate' and 'PreviousFiscalYearStartDate'. How do i calculate YTD and Previous YTD sales total in this case?
Hi srk_powerbi ,
You could try the following measure:
YTD = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]>='Table'[FiscalYearStartDate]&&'Table'[Date]<=NOW()))PREYTD = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]>='Table'[PreFiscalYearStartDate]&&'Table'[Date]<=NOW()))Wish it is helpufl for you!
Best Regards
Lucien
2 Replies
Hi srk_powerbi ,
You could try the following measure:
YTD = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]>='Table'[FiscalYearStartDate]&&'Table'[Date]<=NOW()))PREYTD = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]>='Table'[PreFiscalYearStartDate]&&'Table'[Date]<=NOW()))Wish it is helpufl for you!
Best Regards
Lucien
srk_powerbi , You can use enddate in datesytd. This means start year from first april
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"4/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"4/30"))if you need FY calendar then refer my blog, for any month start calendar
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/1187441Power 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
