Forum Discussion

kreiss's avatar
kreiss
Frequent Visitor
4 years ago
Solved

Weeks Since Store Opening Slider

Howdy!

 

I have a client that want's a dashboard of store inventory in the weeks preceding new store openings and after store opening.

 

I was able to accomplish this by adding a start date to the store file then creating the following measure:

Weeks Before Opening =

VAR OpenDate = Min(POSStore[Store Opening Date])
VAR FilterDate = min('Date'[Date])

Return
DATEDIFF(OpenDate,FilterDate,WEEK)

 

This works fine when I assign a filter to each store visual that includes that measure...not the most efficient...but it worked.

 

But now the client wants a slider with the # of weeks prior to (or after) opening so they can compare the stores inventory levels on a week by week basis both prior to opening and after opening.

 

Since I can't drop the measure into a slider...any thoughts on the easiest way to accomplish this???

Need Help
  • kreiss's avatar
    kreiss
    4 years ago

    I found a video that showed how to do this.

    This was solved by creating an non-related table called "WeeksFromOpen" with one field (also called "WeeksFromOpen" containing just the numbers -10 thru 10.

    I then dropped that field into a slider with range (eg -4 weeks before opening to 3 weeks after opening) and created the following measure:

    Measure Filter =
    Var MinValue = Min(WeeksFromOpen[WeeksFromOpen])
    VAR MaxValue = Max(WeeksFromOpen[WeeksFromOpen])
    VAR CurrentMeasureValue = [Weeks Before Opening]
    RETURN
    IF(CurrentMeasureValue >= MinValue && CurrentMeasureValue<= MaxValue,
    1,
    0
    )

     

    I could then drop this measure into the filter visuals and show only those that calculated as "1".

    Just wanted to close the loop.

     

    Thanks all.

1 Reply

  • kreiss's avatar
    kreiss
    Frequent Visitor
    4 years ago

    I found a video that showed how to do this.

    This was solved by creating an non-related table called "WeeksFromOpen" with one field (also called "WeeksFromOpen" containing just the numbers -10 thru 10.

    I then dropped that field into a slider with range (eg -4 weeks before opening to 3 weeks after opening) and created the following measure:

    Measure Filter =
    Var MinValue = Min(WeeksFromOpen[WeeksFromOpen])
    VAR MaxValue = Max(WeeksFromOpen[WeeksFromOpen])
    VAR CurrentMeasureValue = [Weeks Before Opening]
    RETURN
    IF(CurrentMeasureValue >= MinValue && CurrentMeasureValue<= MaxValue,
    1,
    0
    )

     

    I could then drop this measure into the filter visuals and show only those that calculated as "1".

    Just wanted to close the loop.

     

    Thanks all.