Difference between previous week data

[cham]

Hi ,

I used following measures to calculate difference between previous week data,

table = Calendar (dateStart;dateEnd)

Measure = calculate(sum('Sheet1'[Total]),

filter('Table', 'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)))

Measure 2 = calculate(sum('Sheet1'[Total]),

filter('Table', 'Table'[Column] = WEEKNUM(max('Sheet1'[Date]),1)-1))

Measure 3 = [Measure]/[Measure 2] - 1

But it didnt work, Following are my results,

Regards,

Cham

[Anonymous]

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

Hi @Anonymous 

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

Regards,

Cham

[Anonymous]

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

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]

@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

[cham]

Hi @Anonymous 

I tried the mesaure you mentioned but it did not work. 

Here is my data set,

Date	Spend	Pay
22-07-19	589.32	425.22
24-07-19	3652.85	1833.72
21-07-19	3659.5	2744.64
24-07-19	2312.59	1726.67
24-07-19	519.59	347.25
24-07-19	21178.48	17955.15
24-07-19	3016.45	3734.56
22-07-19	2130.75	1562.65
31-07-19	107.52	68.16
31-07-19	65	45.5
31-07-19	1430.74	1078.24
31-07-19	108.3	15534.32
01-08-19	1011.23	10910.91

I created a mesaure using this data set as below,

%Amount = (SUM('Sheet1'[Spend]) - SUM('Sheet1'[Total Pay])) / SUM('Sheet1'[Spend])

I want to get the diffrence between previous week %Amoun?with this week. How can i do that?Hope this will help you because i provide the data set.

[Anonymous]

@cham - 

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.

[parry2k]

@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