Get previous week value with a condition

[bml123]

HI,

I have data like this as below. For each date, I want to get the  amount of the previous week where Active column is 'Yes'.

Date	Amount	Active	WeekRank
28/01/2022	100	Yes	4
01/02/2022	-50		5

How do I achieve that?

[v-jinweili-msft]

Hi @bml123 ,

1. I have created a  calendar table with two columns, Date and WeekRank for slicer.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "WeekRank", WEEKNUM ( [Date], 2 )
)

2. Create a measure to sum of the week.

Sum of last week =
VAR _week =
    MAX ( 'Calendar'[WeekRank] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Active] = "Yes"
                && WEEKNUM ( 'Table'[Date], 2 ) = _week
        )
)

Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @bml123 

Try this:

Previous Week =

Var _Current_Week = Max('Table'[WeekRank])

Var _Last_Week = _Current_Week-1

return

Calculate(Max('Table'[Amount]),filter(All('Table'),'Table'[WeekRank]=_Last_Week&&'Table'[Active]="Yes"))

Output:

Sample file attached.

Check this link for more details:

https://www.vahiddm.com/post/weekly-time-intelligence-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[bml123]

Hi @VahidDM 

WeekRank is there in a separate calendar table and is not part of 'Table'.

How do I change the formula to reflect that?

[VahidDM]

@bml123 

Can you share a sample of your tables in a text format? and let us know the relationship between tables?

BTW, the easiest way is to use lookupvalue to add a WeekRank column to the main table.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/