Counting Base on Week on Week result - Day in Raw Data

[h_l]

Hi Senior Users,

Can you please help to advise on this.  Sample PBI file is here for download.

In the raw data, there is Clicks by Query, URL by Day

I have created metric for Week on Week changed Clicks:

Clicks WoW Change = 
var lastweeknum = 
CALCULATE(
    MAX('Date'[YearWeek]),
    FILTER(
        ALL('Date'),
        [YearWeek]<MAX('Date'[YearWeek])
    )
)
var lastweekamount = 
CALCULATE(
    SUM(Metrics[Clicks]),
    FILTER(
          ALL('Date'),
          [YearWeek]=lastweeknum
    )
)
return CALCULATE(SUM(Metrics[Clicks])-lastweekamount)

Then, how to get: how many URLs got more or less Clicks in this week compare to last week clicks?

For example,

For week 04-APR, 2 URLs got less Clicks, 9 URLs got more.

For week 11-APR, 3 URLs got less Clicks, 8 URLs got more.

Thank you.

H

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how you want to show the visualization, but I assume you want to display the result on the total row of the matrix visualization.

Please check the below picture and the attached pbix file.

WoW more or less count: =
IF (
HASONEVALUE ( Metrics[URL] )
|| [Clicks WoW Change] = BLANK (),
[Clicks WoW Change],
VAR newtable =
ADDCOLUMNS (
SUMMARIZE ( ALL ( Metrics ), Metrics[URL], 'Date'[YearWeek] ),
"@countdiff", [Clicks WoW Change]
)
RETURN
"Thisweekmore: "
& COUNTROWS ( FILTER ( newtable, [@countdiff] > 0 ) ) & " / Thisweekless: "
& COUNTROWS ( FILTER ( newtable, [@countdiff] < 0 ) )
)

[h_l]

Thanks for the help @Jihwan_Kim , I was thinking just to make another table to list:

Then when apply URL, it can breakdown to see which of the URLs got more/less.

But you've provided very helpful info.

Thanks again.

Have a nice day!

[amitchandak]

@h_l , Use week rank on year week or week start date

example

new columns

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure example

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

[h_l]

@amitchandak  Thanks for the reply, you are always helpful. I will try this!