YTD for same period (current month) Previous Year

[Ramachandran]

Hi,

Currently, I'm getting the sales amount based on Year wise. but I want to show line for YTD of same current month(Oct) Previous Year and similarly Last 2 year (2019) also. I want to report looks like is below

for your reference, I attached .pbix file here Period.pbix - Google Drive

Someone review and give me suggestion how to get achieve on this.

regards,

Ram

[AlexisOlson]

If you have your calendar table set up as a proper date dimension, then you can write

SalesYTD = CALCULATE ( SUM ('Table'[Sales Amt] ), DATESYTD ('Calendar'[Date] ) )
SalesYTD-1 = CALCULATE ( [SalesYTD], DATEADD ('Calendar'[Date], -1, YEAR ))
SalesYTD-2 = CALCULATE ( [SalesYTD], DATEADD ('Calendar'[Date], -2, YEAR ))

For your file, you'll need to create a relationship from 'Calendar' to a date column on 'Table'. I'd recommend defining a new calculated column 

Date = DATEVALUE ( 'Table'[Month] & " " & 'Table'[Year] )

Then set up the relationship like this:

[Ramachandran]

Thanks for your update. After updating the code and I'm getting the report 

But, I want to something different, Is it possible to show the report is like

the report data based on my samples.

fyr, I have attached latest report is here Period_updated.pbix - Google Drive

regards,

Ram

[AlexisOlson · Accepted solution]

Ah. This is a fairly different request than I was thinking.

Try this

SalesYTDMonths = 
VAR ThisYear = CALCULATE ( MAX ('Table'[Year] ), ALLSELECTED ('Calendar' ) )
VAR MonthsYTD = CALCULATETABLE ( VALUES ('Calendar'[Month] ), 'Calendar'[Year] = ThisYear )
RETURN
    CALCULATE ( SUM ('Table'[Sales Amt] ), 'Calendar'[Month] IN MonthsYTD )

SalesRemainderMonths = SUM ('Table'[Sales Amt] ) - [SalesYTDMonths]