YTD and PYTD for custom Fiscal Year

[Anonymous]

Hello,

I'm trying to get proper Fiscal YTD and Prior Fiscal YTD values.

My company have Finscal Year through October to September. I have below monthly date table already in a model, which has all the data I need, as my actual and budget tables have only monthly data.

My Actual and Budget values are just a records of postings to specific accounts and they do not have 'Date' column, but only Fiscal Year and Accouting Period. I created Time Code (TM_CD) which is then used for relationship to Date table. (FISCAL_YEAR * 100 + ACCOUNTING_PERIOD).

I calculate my measers by filtering Actuals/Budget postings by specific account - for exaple my Sales :

CALCULATE(SUM(Actuals[ACTUALS]), FILTER(Actuals, Actuals[ACCOUNT_ID] = 995500))

For my FYTD and PFYTD values I use:

Sales FYTD = CALCULATE([Sales], DATESYTD('Date'[FTI_MTH_END_DT],"09-30"))

Sales PY = CALCULATE([Sales],DATEADD('Date'[FTI_MTH_END_DT], -1, YEAR))

Sales PFYTD = CALCULATE([Sales PY], DATESYTD('Date'[FTI_MTH_END_DT],"30-09"))

which correctly pulls data for Card vizualization and reacts to Quaters/Months slicers.

The issue occurs when I try to pull this data for table or any other vizualization with Time Dimension then for Sales FYTD it adds Agust and September data from Previous Fiscal Year, but it sums up in Total correctly (not including July, August and September 2019)

I was trying to make these Measures with using SAMEPERIODLASTYEAR function but it's not working for this model.

Eventually this report is about to have MTD/QTD/YTD slicer which I will be using to filter specific date frames.

Looks like these measures are properly summing up Sales values but somehow it cannot present a trend using date table.

Could you please help me out?

Thank you,

Krzysztof 

[amitchandak]

@Anonymous , hop month year is coming from the date table. if not you might not get the correct number. Filter nad axis /group by of date should come from the date table

the formula looks correct. Just refer a few examples here

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"9/30"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"9/30"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"9/30"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//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))

. Prefer the way I have used datesytd for the previous year

you can have date in the target table

like date = date([year],[month],1)

or date = date([year],1,1) 

refer this for target and how to create date from year, month year -https://www.youtube.com/watch?v=yPQ9UV37LOU

M way -https://www.youtube.com/watch?v=cJqgphIHXz8

Also check

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)