DAX Measure to calculate all unique/max entries but from previous week

[faustusxanthis]

Hello! 

I have tried searching here but I can't seem to find the right DAX expression/measure for this. 

I have this data: 

EMPLOYEE ID	PAY PERIOD	This week's pay	Hourly Rate
1	5/26/2024	$400	$10
2	5/26/2024	$349	$9
3	5/26/2024	$271	$11
4	5/26/2024	$523	$7
3	5/26/2024	$306	$11
2	5/19/2024	$503	$9
1	5/19/2024	$213	$10
4	5/19/2024	$321	$7
1	5/19/2024	$100	$10

I want to make a measure wherein it will calculate the sum of all hourly rate per unique employee but should calculate the previous week's data. 

For example, in the PowerBI filter, I selected 5/26/2024. I should get the sum of all the hourly rate (1 employee = 1 hourly rate or it could be the MAX of the hourly rate per employee ID) for the previous week (5/19/2024). In this case, if I select 5/26/2024, it should calculate the sum of hourly rate of empoyee 1, 2, and 4 only since they are the only available data from the previous week ($10 + $9 + $7) which is $26. 
If I select 5/19/2024, it should display $0 since there's no data for the previous week. 

I already tried the below code but it always shows a blank value 😞 I assume it's because the data only shows all the entries of the pay period in whatever pay period is selected in the filter. 

VAR Prev_Week = MAX('Example_Table'[DateColumn]) - 7
RETURN
    SUMX(
        SUMMARIZE(
            FILTER(
                'Example_Table',
                'Example_Table'[Pay Period] = Prev_Week
            ),
            'Example_Table'[Employee ID],
            "Total Hourly Rate", MAX('Example_Table'[Hourly Rate])
        ),
        [Total Hourly Rate]
    )

[v-huijiey-msft]

Hi @faustusxanthis ,

Your solution is great, @Ashish_Mathur / @parry2k . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

Please try:

Create a measure:

PreviousWeekHourlyRateSum = 
VAR SelectedWeekStart = MIN('Example_Table'[Pay Period])
VAR PreviousWeekStart = SelectedWeekStart - 7
VAR PreviousWeekEnd = SelectedWeekStart - 1
VAR PreviousWeekData = 
    CALCULATETABLE(
        SUMMARIZE(
            'Example_Table',
            'Example_Table'[Employee ID],
            "MaxHourlyRate", MAX('Example_Table'[Hourly Rate])
        ),
        'Example_Table'[Pay Period] >= PreviousWeekStart,
        'Example_Table'[Pay Period] <= PreviousWeekEnd
    )
RETURN
    SUMX(
        PreviousWeekData,
        [MaxHourlyRate]
    )

The page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[parry2k · Accepted solution]

@faustusxanthis try this:

VAR Prev_Week = MAX('Example_Table'[DateColumn]) - 7
RETURN
    SUMX(
        SUMMARIZE(
            FILTER(
                ALL ( 'Example_Table'[Pay Period], 'Example_Table'[Employee ID] ),
                'Example_Table'[Pay Period] = Prev_Week
            ),
            'Example_Table'[Employee ID],
            "Total Hourly Rate", MAX('Example_Table'[Hourly Rate])
        ),
        [Total Hourly Rate]
    )