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 ...
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)
    )
victoryamaykin's avatar
victoryamaykin
Icon for Advocate I rankAdvocate I
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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-Forum/ba-p/963216

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/1447523

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

    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: 

    EmployeeNameRank User Items Produced Last WeekItems Produced Last Week
    evan brinton12926
    Barbara Bunten22286
    Alan Kester32158
    Vicky Williams42000
    Arlene Swindell51774
    patrick cooksey61454
    Bryan Jones71323
    Amber Eisman81265
    Molly Irwin9569
    Eric Metzler10542
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago
      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's avatar
        victoryamaykin
        Icon for Advocate I rankAdvocate I
        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)
        )