Having problems with previous weeks data

[vsmn]

I can't seem to get this working, has the result is somewhat incorrect.

I would like to display the previous week SUM or AVERAGE. There can be cases of non existent data in the previous week, for example:

Week 40 = 100

Week 39 = 101

Week 37 = 90

In my table I have a column for the week number, and this is the measure I have:

Average Previous Week = 
VAR CurrentWeek = MAX(Carregar[Week of Year])
VAR CurrentTotal = AVERAGE(Carregar[Price])
VAR previous_price = 
    CALCULATE(
        AVERAGE(Carregar[Price]),
        FILTER(
            ALL(Carregar[Week of Year]),
            Carregar[Week of Year] = CurrentWeek - 1
    )
    )
RETURN
IF(
    ISBLANK(previous_price),
    CurrentTotal,previous_price
)

Somehow the result is innacurate:

What am I doing wrong?

[rajendraongole1 · Accepted solution]

Hi @vsmn - I see the problem with your measure. The issue is likely occurring because ALL(Carregar[Week of Year]) removes all filters on the week context, causing unexpected results.

updated measure: can you please check with below

Average Previous Week =
VAR CurrentWeek = MAX(Carregar[Week of Year])
VAR PreviousWeek =
MAXX(
FILTER(
ALL(Carregar),
Carregar[Week of Year] < CurrentWeek && NOT(ISBLANK(Carregar[Price]))
),
Carregar[Week of Year]
)
VAR PreviousWeekPrice =
CALCULATE(
AVERAGE(Carregar[Price]),
Carregar[Week of Year] = PreviousWeek
)
RETURN
IF(
ISBLANK(PreviousWeekPrice),
BLANK(), // or use CurrentTotal if you want to default to current average
PreviousWeekPrice
)

Hope this helps,try applying this measure, and let me know if it resolves the issue in your table.

[vojtechsima · Accepted solution]

Hello, @vsmn ,

here's a simple version:

previousWeekTotal = 
var currentWeek = SELECTEDVALUE('Table'[week])
var previousWeek = MAXX(FILTER(ALL('Table'[week]), 'Table'[week] < currentWeek), 'Table'[week])
var result =  
CALCULATE(
    [total],
    'Table'[week] = previousWeek
)

return result

I would scratch the "-1" check and just get any first value that is lower than current value (week).

Alternative and that'S what you want, you can show blank if it's not the right week like this:

previousWeekTotal = 
var currentWeek = SELECTEDVALUE('Table'[week])
var previousWeek = MAXX(FILTER(ALL('Table'[week]), 'Table'[week] < currentWeek), 'Table'[week])
var result =  
CALCULATE(
    [total],
    'Table'[week] = previousWeek
)
var isFollowing = IF(previousWeek = (currentWeek-1), result, BLANK())

return isFollowing

[rajendraongole1 · Accepted solution]

Hi @vsmn - I see the problem with your measure. The issue is likely occurring because ALL(Carregar[Week of Year]) removes all filters on the week context, causing unexpected results.

updated measure: can you please check with below

Average Previous Week =
VAR CurrentWeek = MAX(Carregar[Week of Year])
VAR PreviousWeek =
MAXX(
FILTER(
ALL(Carregar),
Carregar[Week of Year] < CurrentWeek && NOT(ISBLANK(Carregar[Price]))
),
Carregar[Week of Year]
)
VAR PreviousWeekPrice =
CALCULATE(
AVERAGE(Carregar[Price]),
Carregar[Week of Year] = PreviousWeek
)
RETURN
IF(
ISBLANK(PreviousWeekPrice),
BLANK(), // or use CurrentTotal if you want to default to current average
PreviousWeekPrice
)

Hope this helps,try applying this measure, and let me know if it resolves the issue in your table.