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

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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        cham - Here are some issues:

        1. The visual you show appears to use the date from Sheet1. You will want to use the date from the date table in any visual.

        2. Consider the following measure:

         

        Measure = calculate(
    sum('Sheet1'[Total]),
    filter(
        'Table', 
       'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)
    )
)

        I think you are trying to find the week number of the max date in Sheet1.

         

        1.a. One issue is that WEEKNUM returns a number, but it is being used to filter a date column (I think).

        1.b. Another problem is WEEKNUM function will not work when crossing over years.

        The measure instead could be like this:

         

        Measure = 
--var max_date = CALCULATE(max('Sheet1'[Date]), ALL('Date')) --If you need to only find the most current date in the fact table, you can use these 2 lines.
        --var week_num = CALCULATE(MAX('Date'[WeekNumCumulative]), 'Date'[Date] = max_date)
        var week_num = MAX('Date'[WeekNumCumulative]) --Find the maximum week number associated with the relevant date.
return CALCULATE(
    SUM('Sheet1'[Total]),
    'Date'[WeekNumCumulative] = week_num 
)

        This, of course, requires you to have a Date table that includes a column WeekNumCumulative, that doesn't reset every year. This could be done by calculating the number of weeks between some start date and the date for the row in the date table. Here is an example of a Calculated Column in DAX:

         

        WeekNumCumulative = FLOOR(DATEDIFF(date(2017,1,1),[Date],DAY) / 7,1)

        The previous week measure would be almost identical to the current week, except:

            Date'[WeekNumCumulative] = week_num - 1

        Hope this helps,

        Nathan