Total amount in last 12 months based on a selected month

[Sky]

Hi all,

I am new to DAX and have read quite few blog posts, but still couldn't find a way to calculate a measure by going 12 months prior to a selected month. 

I'm going to calculate total amount for each person in last 12 months based on a selected month. The measure below returns the right amount when the whole year is selected:

YTD Total Amount Person = CALCULATE(sum(CashTransactions[Amount]), DATESYTD('Value Calendar'[CalendarDate]))

How can I calculate a measure based on a month (by going back 12 months prior)? For example if we select 2014-04 in the slicer, it should calculate the total amount one year back of end of the selected month; meaning from 2013-04-30 to 2014-04-30. I have tried DATESYTD function on the calendar date, but it is not showing the right numbers.

 Any help is much appreciated!

[v-yulgu-msft · Accepted solution]

Hi @Sky,

In calendar table, you need an extra column which displays dates 12 month ago.

12 month ago = DATEADD('Value Calendar'[Date],-12,MONTH)

Then, create measures like below:

start date =
CALCULATE (
    MAX ( 'Value Calendar'[12 month ago] ),
    ALLSELECTED ( 'Value Calendar' )
)

end date = MAX('Value Calendar'[Date])

YTD Total Amount Person =
CALCULATE (
    SUM ( CashTransactions[Amount] ),
    FILTER (
        CashTransactions,
        CashTransactions[date] >= MAX ( 'Value Calendar'[12 month ago] )
            && CashTransactions[date] <= MAX ( 'Value Calendar'[Date] )
    )
)

Also, I have uploaded my pbix file for your reference.

Best regards,
Yuliana Gu

[ShivaPrasad1]

@v-yulgu-msft thanks for the solution. I too have similar requirement. I have seen your pbix file where it is showing the start date from 30th or 31st of the month. How can we get the data from 1st of next month as I don't have date in the date column. My date column is like 2018.03

Ex: 

Year: 2017 Month: 04

Required output: 2016.05 to 2017.04(date is not applicable)

Thanks in advance.

Regards,

Shiva

[Sky]

Thanks for your help!