sum weekly total of a page, then compare difference between weeks

[Anonymous]

HI all. I'm trying to compare weekly total of different pages and not able to figure a way to 1) calculate weekly sum 2) setup filter to compare week vs. week.
Hope can get insight from expert here. Thanks a lot.

Raw data:

Date	weekNum	Page	viewNum
2020 Apr 1	week13	A	10
2020 Apr 2	week13	A	10
2020 Apr 8	week14	A	50
2020 Apr 15	week15	A	2
2020 Apr 20	week16	A	100

2020 Apr 1	week13	B	20
2020 Apr 2	week13	B	50
2020 Apr 8	week14	B	10
2020 Apr 15	week15	B	6
2020 Apr 20	week16	B	7
2020 Apr 19	week16	B	13

Output table:

select week 14 vs 13. 

A	30       (=50-20)
B	-60      (=10-70)

select week 16 vs 13. 

A	80       (=100-20)
B	-50      (=20-70)

[V-lianl-msft]

Hi @Anonymous ,

Based on your description, I first created a calculation column to sort the weeks, and then I created two measures, one to calculate the value of the selected week and use as a visual level filter, and the other to compare the week with the week.

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

HI Liang, your solution is exactly what I want.
But I tried to replicate your formula on my actual value, the week total is working fine, but the week difference is not working.

I try to further simpify my question.
What if I have page + date + pageview below, I want to calulate difference based on 2 date picked. How to come up with difference?

Page	Date	PageView	Difference
A	2019 Mar 2	15	- or 0 or 15 is fine.
A	2019 Mar 3	20	5

[V-lianl-msft]

Hi @Anonymous ,

Change the return value to this.

return IF([Measure 2]=min_week,0,max_week-min_week)

Sample .pbix has been updated.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Liang, I can hardly get what's doing here. Would you mind sharing a bit more what each line doing.

Measure =

var min_rank = CALCULATE(MIN('Table'[Column]),FILTER(ALL('Table'),NOT(ISBLANK([Measure 2]))))

var max_rank = CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),NOT(ISBLANK([Measure 2]))))

var max_week = CALCULATE([Measure 2],FILTER(ALLEXCEPT('Table','Table'[Page]),'Table'[Column]=max_rank))

var min_week = CALCULATE([Measure 2],FILTER(ALLEXCEPT('Table','Table'[Page]),'Table'[Column]=min_rank))

return IF([Measure 2]=min_week,0,max_week-min_week)

----------

would be great if you can guide me how to calculate the diff between 2 dates, i.e. how to get the difference column.

Page	Date	PageView	Difference
A	2019 Mar 2	15	- or 0 or 15 is fine.
A	2019 Mar 3	20	5

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

1.Create a new table based on your weeknum as slicer.

Table 2 = DISTINCT('Table'[weekNum])

2.Sort weeknum column

Rank = RANKX(FILTER('Table',EARLIER('Table'[Page])='Table'[Page]),RIGHT('Table'[weekNum],2),,ASC)

3.Calculate the total viewnumber based on the slicer value

sum_viewnumber =
IF (
    MAX ( 'Table'[weekNum] ) IN VALUES ( 'Table 2'[weekNum] ),
    CALCULATE (
        SUM ( 'Table'[viewNum] ),
        ALLEXCEPT ( 'Table', 'Table'[Page], 'Table'[weekNum] )
    )
)

4.Get the max and min weeks of the selected weeknum according to the rank column and set the filter conditions(max_rank,min_rank).

Calculate the total viewnumber of the max and min weeks by page grouping(max_week,min_week)

Measure = 
var min_rank = CALCULATE(MIN('Table'[Rank]),FILTER(ALL('Table'),NOT(ISBLANK([sum_viewnumber]))))
var max_rank = CALCULATE(MAX('Table'[Rank]),FILTER(ALL('Table'),NOT(ISBLANK([sum_viewnumber]))))
var max_week = CALCULATE([sum_viewnumber],FILTER(ALLEXCEPT('Table','Table'[Page]),'Table'[Rank]=max_rank))
var min_week = CALCULATE([sum_viewnumber],FILTER(ALLEXCEPT('Table','Table'[Page]),'Table'[Rank]=min_rank))
return IF([sum_viewnumber]=min_week,0,max_week-min_week)

Again,you can refer to the pbix for more details.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks mate.

I tried with 

ThisWeekTotal = CALCULATE (

SUM ('All'[viewNum]),

FILTER (

ALL ( 'All' ),

'All'[WeekNum] = MAX ( 'All'[WeekNum] )

)

)

Seems it sum up all my A & B page together.
I want to see A's weekly & B's weekly total seperately.

[amitchandak]

@Anonymous 

You should have a week in a different table.

If you add page(A or B) in visual, it should display value separately

[amitchandak]

Refer, if this can help, How I used week Rank to get current and Prior

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0