cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sky Frequent Visitor
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]))

YTD by Year.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

YTD by month.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Any help is much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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] )
    )
)

 

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

 

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

 

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

 

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.
Sky Frequent Visitor
Frequent Visitor

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

Thanks for your help!

ShivaPrasad1 Regular 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)

 

Thanks in advance.

 

Regards,

Shiva