Forum Discussion

electrobrit's avatar
electrobrit
Icon for Post Patron rankPost Patron
7 years ago

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 ...
Need Help
electrobrit's avatar
electrobrit
Icon for Post Patron rankPost Patron
7 years ago

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's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago
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's avatar
    electrobrit
    Icon for Post Patron rankPost Patron
    7 years ago

    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's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago
    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's avatar
    electrobrit
    Icon for Post Patron rankPost Patron
    7 years ago

    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.