Forum Discussion

victoryamaykin's avatar
victoryamaykin
Icon for Advocate I rankAdvocate I
2 years ago
Solved

RANKX for Measure Calculated with DatesBetween()

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. 

 

DateTeamMemberItemsStocked
8/11/2024Vlad Biford2926
8/11/2024Jo Peotz2286
8/11/2024Zsa zsa Lewing2158
8/11/2024Drucy Ivanin2000
8/11/2024Waylen Faint1774
8/15/2024Mikel Northidge1454
8/15/2024Fayre Winterson1323
8/15/2024Kelcie Guy1265
8/15/2024Clarissa Topliss569
8/15/2024Juline Ganter542


It's not working as expected.

TeamMemberItemsStockedRank
Vlad Biford29264
Jo Peotz22869
Zsa zsa Lewing215810
Drucy Ivanin200014
Waylen Faint177420
Mikel Northidge145428
Fayre Winterson132339
Kelcie Guy126545
Clarissa Topliss569142
Juline Ganter542150



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.
Need Help
  • victoryamaykin's avatar
    victoryamaykin
    2 years ago

    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)
    )

7 Replies