Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
electrobrit
Post Patron
Post Patron

week over week but same day comparison

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).

DateCase closure time (days) this weekCase closure time (days) previous Week
Saturday, October 27, 20185 
Sunday, October 28, 20186 
Monday, October 29, 20188 
Tuesday, October 30, 20184 
Wednesday, October 31, 20185 
Thursday, November 1, 20183 
Friday, November 2, 20182 
Saturday, November 3, 2018325
Sunday, November 4, 2018246
Monday, November 5, 2018198
Tuesday, November 6, 2018804
Wednesday, November 7, 2018505
Thursday, November 8, 2018543
Friday, November 9, 2018432
Saturday, November 10, 2018132
Sunday, November 11, 2018224
Monday, November 12, 2018319
Tuesday, November 13, 2018180
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
14 REPLIES 14
PattemManohar
Community Champion
Community Champion

@electrobrit Please try this as a "New Column"

 

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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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!

@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.




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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. 

@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)




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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?

@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.




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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!!!

@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.




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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

 

@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.




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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. 

@PattemManohar did you have any feedback?

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.