Getting Sales from 2 Months Ago - Context Errors

[chillbro5587]

Hi all. I'm having a lot of trouble with something that shouldn't be too hard. I've tried for about 6 hours and haven't gotten past where I was after 1 minute.

Problem: Get sales from 2 months ago, be able to display:

1. In a table(4/1/2018 shows 2/1/2018)

2. As a standalone "Card" with sales from 2 months ago from today.

#1 is fine. See below

Date	Amount	Sales 2 Months Ago
1/1/2018	200
2/1/2018	300
3/1/2018	400	200
4/1/2018	500	300

#2 simply shows a sum of all sales instead of the sales from 2 months ago

Tables: Date and Sales, joined on Date['DateValue'] and Sales['Date']

(Measure) Sales 2 Months Ago:=CALCULATE(SUM(Amount),DATEADD(Date['DateValue'],-2,Month))

What I'm really trying to do is get the sales from the entire month of 2 months ago. It is July now, so I'd want to get sale from:

5/1/2018 to 5/31/2018

• Edit: I also want this to be able to go into a table, such as in #1

Any help would be appreciated.

[v-piga-msft]

Hi @chillbro5587,

The measure from  Greg_Deckler should solve your problem.

If you have solved this problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share the output you expected, so that we can help further investigate on it?

Best Regards,

Cherry

[Greg_Deckler · Accepted solution]

You should be able to use the techniques as described in my Quick Measure, Time Intelligence, The Hard Way:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

So, basically:

Sales 2 Months Ago = 
VAR __month = MONTH(MAX([Date]))
VAR __tmpTable = FILTER(ALL(Table11),MONTH([Date])=__month-2)
RETURN
SUMX(__tmpTable,[Amount])

[chillbro5587]

What happens if the month is January? 1-2=-1. Does that work?