topic Re: How to create a measure of last month of each year in a Tabular SSAS database in DAX Commands and Tips

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

FBAB — Wed, 28 Apr 2021 10:22:08 GMT

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.

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

amitchandak — Wed, 28 Apr 2021 10:43:29 GMT

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"))

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

FBAB — Wed, 28 Apr 2021 12:45:01 GMT

Hi amitchandak,

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

Thank you 😊