RANKX for Measure Calculated with DatesBetween()

[victoryamaykin]

I'm making a leaderboard for the backroom where employees tag donations as they get sorted. Each item tagged is said to be "produced". Here's an exampe of the table called FactUserProduction. I have a DimDates and DimUser tables connected to this fact table.  I need the rank of the employees for the Items they produced last week. These names are fake data. Sorry that I can't provide a test pbix file. 

Date	TeamMember	ItemsStocked
8/11/2024	Vlad Biford	2926
8/11/2024	Jo Peotz	2286
8/11/2024	Zsa zsa Lewing	2158
8/11/2024	Drucy Ivanin	2000
8/11/2024	Waylen Faint	1774
8/15/2024	Mikel Northidge	1454
8/15/2024	Fayre Winterson	1323
8/15/2024	Kelcie Guy	1265
8/15/2024	Clarissa Topliss	569
8/15/2024	Juline Ganter	542

It's not working as expected.

TeamMember	ItemsStocked	Rank
Vlad Biford	2926	4
Jo Peotz	2286	9
Zsa zsa Lewing	2158	10
Drucy Ivanin	2000	14
Waylen Faint	1774	20
Mikel Northidge	1454	28
Fayre Winterson	1323	39
Kelcie Guy	1265	45
Clarissa Topliss	569	142
Juline Ganter	542	150

Here's the DAX code as I have it now: 

Rank User Items Produced Last Week :=

VAR _start =

    DATEADD(

        DimDates[Start of Week], -7 , DAY

    )

VAR _end =

    DATEADD(

        DimDates[End of Week], -7 , DAY

    )

VAR _rank_items_produced =

    RANKX(

       

         ALL(DimUser[EmployeeName]),

        CALCULATE(

            SUMX(FactUserProduction,[ItemsStocked]),

            DATESBETWEEN(DimDates[Date], _start, _end)

        ),        ,

        DESC,

        Dense

    )

RETURN

IF(

    HASONEVALUE(FactUserProduction[TeamMember]),

    _rank_items_produced

)

Any help you can provide is appreciated. I'm using the hasonevalue() to remove the total for rank in the table.

[lbendlin]

hard to help without seeing the date table definition or the data model.  Most likely you need to replace 

ALL(DimUser[EmployeeName])

with

ALLSELECTED(DimUser[EmployeeName])

[victoryamaykin]

Thanks for your response. This solution works in my semantic model file, but for some reason, it will not work the same way in the report file. Any ideas? How do I upload a pbix file? 
I am using a page filter with a calculated column called "isToday" to get today's results. There is another filter for the department as well. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[victoryamaykin]

PBIX file: 
https://drive.google.com/file/d/1zSnhbSKhsHYeJ5iAO0SGvRdqqSUrwU5y/view?usp=sharing 

Data: 
https://docs.google.com/spreadsheets/d/1cFX4lYAsa1AS1FtFIdx7qG6aWUZgAF_LJB3FCZ7hGAY/edit?usp=sharing...

Expected outcome: 

EmployeeName	Rank User Items Produced Last Week	Items Produced Last Week
evan brinton	1	2926
Barbara Bunten	2	2286
Alan Kester	3	2158
Vicky Williams	4	2000
Arlene Swindell	5	1774
patrick cooksey	6	1454
Bryan Jones	7	1323
Amber Eisman	8	1265
Molly Irwin	9	569
Eric Metzler	10	542

[lbendlin]

SELECTEDVALUE(DimDates[Start of Week])  has no meaning as you don't have a filter on the date and the calendar table is connected to the data model.  This would only work if you had a date picker slicer AND if it would be fed by a disconnected table.

a simple ranking formula would be

Rnk = rankx(allselected(DimUser[EmployeeName]),calculate(sum(FactUserProduction[Items Produced])))

Note that the calculation needs to happen inside the RANKX, not before.

[victoryamaykin · Accepted solution]

I have a date filter calculated column called "isToday" to get the current day. The start and end of week is based on that date in the DimDates table. The DimDates table is much more involved than what I put in the sample file. 

Is there another way to get items produced last week? Here's what I've been doing: 

Items Produced Last Week =

VAR _start =

    DATEADD(

        DimDates[Start of Week], -7 , DAY)

// error message here says parameter is not the correct type, but it was working before

VAR _end =

    DATEADD(

        DimDates[End of Week], -7 , DAY)

RETURN

CALCULATE(

    SUM(FactUserProduction[ItemsStocked]),

    DATESBETWEEN(DimDates[Date], _start, _end)

)

[lbendlin]

VAR _start =
    DATEADD(
        DimDates[Start of Week], -7 , DAY)

This returns a table, not a scalar value. Not something you can use in DATESBETWEEN.

Something like 

MIN(DimDates[Start of Week])-7

• might work. But since you already have a calculated column for "IsToday" then you can add another one for "IsInLastWeek".