custom YTD calculation help

[srk_powerbi]

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?

[v-luwang-msft · Accepted solution]

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

[amitchandak]

@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-Calend...

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