week over week but same day comparison

[electrobrit]

I have 2 tables: Date and Cases which is daily detail of the cases coming in daily.

I would like a simple line chart to show weekly comparison with the past 7 days and a line comparing the previous 7 days. 
This would be whatever I'm measuring-total cases opened, closed, by team, closure time, etc.


Need help with the measures to do this, nothing I'm trying is working. Someone previously gave me total for prev week but this is not what I needed. I wanted daily (like below).

Date	Case closure time (days) this week	Case closure time (days) previous Week
Saturday, October 27, 2018	5
Sunday, October 28, 2018	6
Monday, October 29, 2018	8
Tuesday, October 30, 2018	4
Wednesday, October 31, 2018	5
Thursday, November 1, 2018	3
Friday, November 2, 2018	2
Saturday, November 3, 2018	32	5
Sunday, November 4, 2018	24	6
Monday, November 5, 2018	19	8
Tuesday, November 6, 2018	80	4
Wednesday, November 7, 2018	50	5
Thursday, November 8, 2018	54	3
Friday, November 9, 2018	43	2
Saturday, November 10, 2018	1	32
Sunday, November 11, 2018	2	24
Monday, November 12, 2018	3	19
Tuesday, November 13, 2018	1	80
Wednesday, November 14, 2018		50
Thursday, November 15, 2018		54
Friday, November 16, 2018		43
Saturday, November 17, 2018		1
Sunday, November 18, 2018		2
Monday, November 19, 2018		3
Tuesday, November 20, 2018		1

[PattemManohar]

@electrobrit Please try this as a "New Column"

CaseClosurePrevWeek = LOOKUPVALUE(Test79PrevDay[CaseClosureThisWeek],Test79PrevDay[Date],DATEADD(Test79PrevDay[Date],-7,DAY))

[electrobrit]

Thank you @PattemManohar. One issue, I don't know how to solve. My case closure time is in the cases table 'duration_days'

Using your calc column-i used this below
CaseClosurePrevWeek = LOOKUPVALUE(VW_Cases[Duration_Days], 'Date'[Date],DATEADD('Date'[Date],-7,DAY)) 

I am getting this message
A table of multiple values was supplied where a single value was expected.

What am I doing wrong?

Thanks again!

[PattemManohar]

@electrobrit I believe the "Date" field and "Duration_Days" in the sample data are in the same table. Is that not the case ? Please confirm.
I can see that you are using two different tables in the DAX expression. The DAX that I've provided is based on the assumption that both fields are in the same table.

[electrobrit]

@PattemManohar I like what you did though. My original data table I was showing what I wanted to be able to get to in the solution. Sorry about that.
They are 2 different tables. I have a date table and the case table and have related them. 

[PattemManohar]

@electrobrit Ok !! Then, on what field you have linked Case table and Date Table. Is that Date field ? then you must have Date Field in Case Table as well, so you can use that as part of your LOOKUPVALUE expression instead from Date Table. If that is not the case then please share more details about case table (ideally data model)

[electrobrit]

@PattemManohar thank you so much, sorry for being vague. Maybe this is why I'm having trouble with this calculation! 

I have a date table and a case table with with a date (Date of Case Creation) which is how the tables are related
DateTable(Date) and CaseTable(CaseCreated)

So you are saying to just use that CaseCreated date?

[PattemManohar]

@electrobrit Yes please, I don't see any reason why you can't use the Date field in Case Table which will solve your problem doing so. Give a try with that and lets see if you have any issues.

[electrobrit]

@PattemManohar

CaseClosurePrevWeek=LOOKUPVALUE(VW_Cases[Duration_Days],VW_Cases[CaseCreatedOn],DATEADD(VW_Cases[CaseCreatedOn],-7,DAY)) 

getting the same error.  "A table of multiple values was supplied where a single value was expected".

any thoughts and thanks, you've been helpful!!!

[PattemManohar]

@electrobrit Does the table contain duplicate values in "CaseCreatedOn" date field. The sample data provided contains unique values and the logic is working fine for that. If there are duplicates then it does require a tweak to it. Please check and confirm.

[electrobrit]

it would have duplicate values because multiple cases are created on any given date. I apologize that table is confusing, that's not the data, it was to conceptualize the result I needed. 

Any thoughts on going back to using the DATE table, this contains no dups. OR SUGGESTIONS?

@PattemManohar

[PattemManohar]

@electrobrit I would like to see your data model and also the table structure of case table or please provide the exact sample data to replicate your issue.

[electrobrit]

@PattemManohar
I can't thank you enough. I'm sure I'm learning something big here but I have spent so much time and still no avail.

Attached or via link is a sample pbix report with similar data.

Sample pbix

Again, thank you in advance. 

[electrobrit]

@PattemManohar did you have any feedback?

[electrobrit]

am I missing something in DurationDays? this is how long a case is open. 
I can't figure out what to do.