cancel
Showing results for
Did you mean:
Frequent Visitor

## Total amount in last 12 months based on a selected month

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Total amount in last 12 months based on a selected month

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] )
)
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Total amount in last 12 months based on a selected month

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] )
)
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Regular Visitor

## Re: Total amount in last 12 months based on a selected month

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