Difference between previous week data

Question

Hi ,&nbsp;I used following measures to calculate difference between previous week data,&nbsp;table = Calendar (dateStart;dateEnd)&nbsp;Measure = calculate(sum('Sheet1'[Total]),filter('Table', 'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)))&nbsp;Measure 2 = calculate(sum('Sheet1'[Total]),filter('Table', 'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)-1))&nbsp;Measure 3 = [Measure]/[Measure 2] - 1&nbsp;But it didnt work, Following are my results,&nbsp;Regards,Cham

parry2k · Answer

cham I know&nbsp;Anonymous solution will work but one should always add Datetable in your model and work thru that for all time intelligence related reporting.
&nbsp;
There are many way to add date table in your table either using DAX or M

anonymous · Answer

cham&nbsp;- No, I was substituting the measure 2 with the one I mentioned.Also,&nbsp;parry2k&nbsp;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.

anonymous · Answer

cham&nbsp;- 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:&nbsp;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.&nbsp;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:&nbsp;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:&nbsp;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 - 1Hope this helps,Nathan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

cham&nbsp;-&nbsp;To compare 2 different time periods, you need to "modify the filter context". The way to do this is with CALCULATE function, like this:%Amount vs Previous Week = var prev_week_num = MAX('Date'[WeekNumCumulative]) - 1 --Find the maximum week number associated with the relevant date and subtract one to get the previous week.var prev_week_value = CALCULATE(    [%Amount],     'Date'[WeekNumCumulative] = week_num)return [%Amount] - prev_week_value I hope this helps. If it does, please Mark as a solution.I also appreciate Kudos.

anonymous · Answer

cham&nbsp; - 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

Forum Discussion

Difference between previous week data

8 Replies

Related content

Calculate difference from previous month

calculate % difference from previous quarter

Different schema into different destination

Calculate Difference Between Previous Row

Orderbook adjusted for previous periods differences