Forum Discussion

cham's avatar
cham
Post Patron
7 years ago

Difference between previous week data

Hi ,   I used following measures to calculate difference between previous week data,   table = Calendar (dateStart;dateEnd)   Measure = calculate(sum('Sheet1'[Total]), filter('Table', 'Table'...
General Comment
Show and Tell
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
7 years ago

cham  - You can use a variable in your "previous week" measure:

Measure 2 = 
var prev_week_num = WEEKNUM(max('Sheet1'[Date]),1)-1
calculate(
    sum('Sheet1'[Total]),
    'Table'[Column] = prev_week_num 
)

Hope this helps,

Nathan

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    cham I know Anonymous solution will work but one should always add Datetable in your model and work thru that for all time intelligence related reporting.

     

    There are many way to add date table in your table either using DAX or M

  • cham's avatar
    cham
    Post Patron
    7 years ago

    Hi Anonymous 

     

    Do I need to only use this measure to calculate the difference between previous week data?

     

    Regards,

    Cham

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      cham - No, I was substituting the measure 2 with the one I mentioned.

      Also, parry2k gave good advice about adding a date table to your model. That table can include WeekNumber and it would be best to also have a "RelativeWeek" or "WeekKey" which is what you would use to calculate the previous week. If you need assistance with that, let us know.

      The solution I provided will not actually work when the weeks go across years.

      • cham's avatar
        cham
        Post Patron
        7 years ago

        Hi Anonymous 

         

        Yes I alredy create a new calender in my sheet and i make a relationship between calender table and my sheet date. I want to know what is wrong with my measures which i show in earlier. Can you please explain it to me?