Running 12 Month Revenue vs Previous 12 months

[nathanhanser]

Hello Everyone,

I have two tables, one that is a clendar with dates/Month fields, and another that has sales info.  Here is an example of the sales table:

Sales Date |  Bill Amount
----------------------------------
12/1/2015     |  5
12/11/2015   |  10

1/1/2016       |  5

2/1/2016       |  12
6/1/2016       |  2
7/1/2016       |  4
1/1/2017       |  8
5/1/2017       |  10
6/1/2017       |  15
7/1/2017       |  2

What I need to do is create two measures that show running totals over renning 12 month period.  So if today was 9/30/2017, I would need to show data like this:

Month/Year   | Sales TY  | Sales LY
----------------------------------------------
10/16            |   0            |   5
11/16            |   0            |   0
12/16            |   0            |   10
1/17              |   8            |   5
2/17              |   0            |   12
3/17              |   0            |   0
4/17              |   0            |   0
5/17              |   10          |   0
6/17              |   15          |   2
7/17              |   2            |   4
8/17              |   0            |   0
9/17              |   0            |   0

I have tired using a calculate with datesbetween like this, but I can't seem to get it working when I use measures to calculate dates rather than hard coding dates:

Sales TY = CALCULATE(SUM('Bill Amount'),
DATESBETWEEN(
'Calendar'[Date],
"10/1/2016",
"9/30/2016"),
ALL('Calendar'[Date]))

Any and all helo us much appreicated!

[Phil_Seamark · Accepted solution]

Hi @nathanhanser

In the sample you posted, the expected output isn't a running total, rather it's the sales for that month (not cumulative).

If that is the case you can create the following two calculated measures

Sales TY = SUM('Table1'[Bill Amount])

Sales LY = CALCULATE(
					SUM('Table1'[Bill Amount]),
					SAMEPERIODLASTYEAR('Dates'[Date])
					)

and then drag them to a table along with a column for Month from your related date/calendar table.

Here is a link to download an example PBIX file

https://1drv.ms/u/s!AtDlC2rep7a-oEHaSI23CefHfxS6