MTD Using a Date Table

[Anonymous]

Hi, 

I have created a date table with the year 2016 - 2017. It has a column for the year, month, week, and day. 

I use the chiclet filter in my report. So the user chooses the month, year, and week separately. When a July filter is chosen and the year is filtered for 2016 and 2017; the data shows the complete month last year, and only partial for the current year. How do I show month to date for last year?

[v-yulgu-msft]

Hi @Anonymous,

Suppose you have created a one to many relationship between source table and date table. To compare MTD value in currect year and previous year, you could refer to below measures:

TY MTD = CALCULATE(SUM('MTD'[SalesAmount]),DATESMTD('Date table'[Date]))
LY MTD = CALCULATE([TY MTD],SAMEPERIODLASTYEAR('Date table'[Date].[Date]))

Here is a blog for your reference: Power BI Tips: Calculating Year To Date Values

Best regards,
Yuliana Gu