topic Re: Having problems with previous weeks data in Desktop

Having problems with previous weeks data

vsmn — Sun, 03 Nov 2024 15:40:46 GMT

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?

Re: Having problems with previous weeks data

rajendraongole1 — Sun, 03 Nov 2024 16:11:12 GMT

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.

Re: Having problems with previous weeks data

vojtechsima — Sun, 03 Nov 2024 16:34:31 GMT

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: