Objective: I would like obtain the difference between current and previous sessions based on date slicers I want the output to be 4 columns as such:
- Date
- Current Sessions (see measure below)
- Previous Sessions (see measure below)
- Difference (no measure calculated yet).
Situation: I currently have two measures
- Current Sessions:
SUM(Sales[Sessions]) - Previous Sessions (thanks to @Alexis Olson):
VAR datediffs = DATEDIFF( CALCULATE (MAX ( 'Date'[Date] ) ), CALCULATE (MAX ('Previous Date'[Date])), DAY ) RETURN CALCULATE(SUM(Sales[Sessions]), USERELATIONSHIP('Previous Date'[Date],'Date'[Date]), DATEADD('Date'[Date],datediffs,DAY) )
I have three tables.
- Sales
- Date
- Previous Date (carbon copy of Date table)
My previous date table is 1:1 inactive relationship with the Date table. Date table is 1 to many active relationship with my Sales Table.
I have two slicers at all time comparing the same amount of days from different time periods (e.g. Jan 1th to Jan 7th 2019 vs Dec 25st to Dec 31th 2019) If i put current sessions, previous sessions and a date column from any of the three tables
+----------+------------------+-------------------+------------+
| date | current sessions | previous sessions | difference |
+----------+------------------+-------------------+------------+
| Jan 8th | 10000 | 70000 | 3000 |
| Jan 9th | 20000 | 10000 | 10000 |
| Jan 10th | 15000 | 16000 | -1000 |
| Jan 11th | 14000 | 12000 | 2000 |
| Jan 12th | 12000 | 14000 | -2000 |
| Jan 13th | 11000 | 16000 | -5000 |
| Jan 14th | 15000 | 18000 | -3000 |
+----------+------------------+-------------------+------------+
When I put the Sessions date on the table along with sessions and previous sessions, I get the sessions amounts right for each day but the previous session amounts doesn't calculate correctly I assume because its being filtered by the date rows.
How can I override that table filter and force it to get the exact previous sessions amounts? Basically have both results appended to each other.The following shows my problem. the previous session is the same on each day and is basically the amount of dec 31st jan 2018 because the max date is different for each row but I want it to be based on the slicer.
