Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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,
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,
That worked instantly! Thank you! This solution is so elegant and easy 🙂
Regards GastroStuermer