How to create a measure of last month of each year in a Tabular SSAS database

[FBAB]

Hi,

I would like to get a measure that calculates [Margin %] of every month - [YTD Margin Dec Last Year] of december last year

My idea is to create a new hiden measure [YTD Margin Dec Last Year], and then i calculate ([Margin %] of every month - new hiden measure), but i havn't found how

YTD Margin Dec Last Year:= 
IF ( 
   MONTH ( MAX ('Date'[Date Field] ) ) = 12;
   CALCULATE([YTD Margin %]; SAMEPERIODLASTYEAR('Date'[Date Field])
))

FrstCal:= [Margin %] - [YTD Margin Dec Last Year]  -- work only for Month num 12

example :

I hope its clear

Thank you.

[amitchandak · Accepted solution]

@FBAB , do you dec MTD ?

MTD (Last Year End) = CALCULATE([Margin %],DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"12/31")))

last year last month = CALCULATE([Margin %],DATESMTD(dateadd('Date'[Date],-1*month(Today()),MONTH)))

YTD Sales = CALCULATE([Margin %],DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE([Margin %],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

[FBAB]

Hi @amitchandak,

I only need MTD (Last Year End), because I have already calculated the other measures.

Thank you 😊