Forum Discussion

bml123's avatar
bml123
Post Patron
4 years ago

Get previous week value with a condition

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 ...
Need Help
v-jinweili-msft's avatar
v-jinweili-msft
Microsoft Employee
4 years ago

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.