Compare data of one week with the data of the week before, while the date is chosen via slicer

[GastroStuermer]

Hi,

I've got some Google Searchconsole Data and I want to create a tile that compares the Average Position on a certain date, which is chosen by a slicer, with the Average Position one week earlier. The following just gives me a "(Blank)":

AveragePositionOneWeekEarlier = CALCULATE ( [AveragePosition] ; FILTER ( ALL (GSC[Date]) ; WEEKNUM( GSC[Date] ; 21) = [WeekSelected] - 1))

I tried inserting an "ALL" after "WEEKNUM":

AveragePositionOneWeekEarlier = CALCULATE ( [AveragePosition] ; FILTER ( ALL (GSC[Date]) ; WEEKNUM( ALL (GSC[Date]) ; 21) = [WeekSelected] - 1))

but then I just get an error about multiple Values while only one is expected.

The Measures used are the following: 

Average Position = AVERAGE('GSC'[Position])

WeekSelected = MAX('OtherTable'[Weeknumber])

Can someone help me with that?

[v-sihou-msft · Accepted solution]

@GastroStuermer

Since you have date column in your table, you can add WeekNum column and Year column for your calculation.

WeekNumber = WEEKNUM('Table'[Date],21)

Year = YEAR('Table'[Date])

Then you can just create two measures to calculate Current Week AVG and Previous Week AVG.

Current Week AVG = CALCULATE(AVERAGE('Table'[Amount]),FILTER(ALL('Table'),'Table'[WeekNumber]=MAX('Table'[WeekNumber]) && 'Table'[Year]=MAX('Table'[Year]))) 

Previous Week AVG = CALCULATE(AVERAGE('Table'[Amount]),FILTER(ALL('Table'),'Table'[WeekNumber]=MAX('Table'[WeekNumber])-1 && 'Table'[Year]=MAX('Table'[Year]))) 

Regards,

[GastroStuermer]

HI @v-sihou-msft

That worked instantly! Thank you! This solution is so elegant and easy 🙂

Regards GastroStuermer