YTD calculation custom Calendar table

[Anonymous]

Hi

Would the following pattern work to calculate a YTD value given the Calendar table below?

CALCULATE(
[Gross Written Premium],
FILTER( ALL('SSAS V_DIM_Calendar'),
'SSAS V_DIM_Calendar'[ReportingDateStart] >= MAX('SSAS V_DIM_Calendar'[FinancialYearStart]) &&
'SSAS V_DIM_Calendar'[ReportingDateEnd] <= MAX('SSAS V_DIM_Calendar'[ReportingDateEnd])
)
)

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Report Start ]>='Table'[Fisical Start]&&'Table'[Report End]<='Table'[Fisical End]))

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , If you can have column like

Day of Year =datediff([Year Start date] , [Date],Day) +1

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

or with month

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

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