Show Results Before/After Selected Date Range

[Anonymous]

Hi, Thanks @v-kelly-msft  and  @Ashish_Mathur  for the assistance on this issue. On a high level, I would like to select a date range on a slicer and use the elapsed days, on that selected date range, to see data before and after.

I have a slicer on my dashboard:

example: I selected 11/2/20 and 11/13/20 as my date range. The elapsed days is 12 days.

I also have a what-if parameter which controls the seasons, or how many date ranges you need to see before/after that current selected date range. Picture below, 2 prior seasons is selected so 24 days prior 11/2/20 is the scope.

I would like to visually show that 12 day period and 2 seasons before it. The 2 seasons will share the same elapsed days as the current date range on the slicer: 12 days.

keep in mind the date range can change based on the user's choosing, so selecting a 5 day range will change the seasons to be 5 days each.

I share my pbix file with sample tables: Show-Results-Before-After-Selected-Date-Range.pbix 

I appreciate the help! Thanks!

[v-kelly-msft]

Hi  @Anonymous ,

I didnt see your pic,would you pls attach it again?Btw,could you pls provide some sample data for test?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft , I reattached relevant pictures and modified the description/issue. Please let me know if need more info. thanks , again!

[Ashish_Mathur]

Hi,

What do you mean by "the bar graph visual will only be 3 bars with each their own 12 days ranges"?

[Anonymous]

Hi @Ashish_Mathur , I modified the description/issue. Please let me know if need more info. thanks!

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Sorry for the late reply.

Delete the relationship between "Calendar lookup" and "Customers":(as it will affect the date range showing)

Then create a measure as below:

Measure =
VAR _newperiod =
    SELECTEDVALUE ( 'Prior Seasons'[Prior Seasons] ) * [Elapsed Days]
VAR _mindate =
    CALCULATE ( MIN ( 'Calendar Lookup'[Date] ), ALLSELECTED ( 'Calendar Lookup' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Calendar Lookup'[Date] ), ALLSELECTED ( 'Calendar Lookup' ) )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Customers (Fact Table)'[Date] ),
        FILTER (
            ALL ( 'Customers (Fact Table)' ),
            'Customers (Fact Table)'[Date] >= _mindate - _newperiod
                && 'Customers (Fact Table)'[Date] <= _maxdate
        )
    )
RETURN
    IF ( MAX ( 'Customers (Fact Table)'[Date] ) IN _tab, 1, BLANK () )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

This is great, @v-kelly-msft  !! Do you know if its possible to keep the calendar table but keep the functionality as in your solution? I'm considering using this on another report but that other report has been structured around the calendar lookup and fact table. Thank you for any tips/advice!

Thanks again for the help, I really appreciate it 😄 

[Anonymous]

I figured it out. Your solution really helped me see it through. Thanks!