Sum of Dates between Transaction Date - 1 Year & Transaction Date

[Anonymous]

I am trying to get a measure that is the sum of sales between the Date of the Transaction for a row and the last year (365 days).

I tried:

Trailing12Months = (CALCULATE(sum(PS_SalesHistory[Project Funding]), DATESINPERIOD(PS_SalesHistory[Month].[Date], LASTDATE(PS_SalesHistory[Month].[Date]), -1, YEAR)))

But this is only giving me the dates between the current date and the prior 365.

I tried creating a new table, but I'm getting an error there too:

Trailing12Months = ADDCOLUMNS(PS_SalesHistory, "Trailing12Months", DATESINPERIOD(PS_SalesHistory[Month].[Date], LASTDATE(PS_SalesHistory[Month].[Date]), -1, YEAR))

[v-xiaotang · Accepted solution]

Hi @Anonymous 

you can use the function DATESINPERIOD() & EDATE(), and it will the period you need, for example,

so you can do sum calculation for last 12 months by creating the measure bellow, e.g.

Trailing12Months = 
var _currentdate=EDATE(MIN(PS_SalesHistory[Month]),-1) 
return 
CALCULATE(sum(PS_SalesHistory[Project Funding]), DATESINPERIOD(PS_SalesHistory[Month],_currentdate,-1,YEAR))

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[Anonymous]

Moving this back to the top of the list to see if anyone knows how to solve it

[Anonymous]

@amitchandak       

I tried both your formulas and its not giving me what I'm looking for.

Last Year Sales is giving me the total for each Fiscal Year.

Year Behind Sales is giving me the total for the Calendar Year.

I need the sum of the sales for the last 12 months for each row.

For example:

Basic Data:

1/1/2020 3
2/1/2020 4
3/1/2020 5
4/1/2020 6
5/1/2020 7
6/1/2020 8
7/1/2020 1
8/1/2020 2
9/1/2020 3
10/1/2020 4
11/1/2020 5
12/1/2020 6
1/1/2021 7
2/1/2021 8
3/1/2021 9
4/1/2021 10
5/1/2021 12

This would give us:

1/1/2021 51
2/1/2021 54
3/1/2021 57
4/1/2021 60
5/1/2021 63

Basically the calculation for 1/1/2021 is the last 12 months added togethor:

1/1/2020 3

2/1/2020 4
3/1/2020 5
4/1/2020 6
5/1/2020 7
6/1/2020 8
7/1/2020 1
8/1/2020 2
9/1/2020 3
10/1/2020 4
11/1/2020 5
12/1/2020 6

And the calculation for 2/1/2021 is the prior 12 months would sum 2/1/2020 to 1/1/2021

3/1/2021 would sum 3/1/2020 to 2/1/2021

Let me  know if you have any questions

[amitchandak]

@Anonymous , if you need last year , not rolling 365 days

rolling your formula should work

Last year Sales = CALCULATE(sum(PS_SalesHistory[Project Funding]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Year behind Sales = CALCULATE(sum(PS_SalesHistory[Project Funding]),previousyear('Date'[Date]))

use date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.