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

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

 

 

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".