PTD/YTD still includes custom date range

[Stefancy]

Hello!  I’m hoping someone has seen this before.

I am using a date button slicer with three options: Custom, PTD, YTD.  I have a separate date slicer that users can use to input a custom date range.  I have the second date slicer set to only be active when the Custom option is selected in the first slicer.  The button slicer is allowed to interact with/affect the second (date range) slicer, but the date range slicer is set to not be able to interact with the button slicer.

The problem I’m having is that when a custom date range is input in the date range slicer and then YTD or PTD are selected in the button slicer, the date range does not revert back to being inactive/greyed out.  In the case of a YTD selection, the other visuals use the date range that is input as a sub selection of dates.

Example:

I input custom date range 3/1/2021 to 5/13/21 and see the results:

Then I click YTD and see no change:

If I click YTD BEFORE entering any custom date range, it works fine. It makes the custom date range inactive:

Before:

After:

I have deleted and re-added these slicers many times, editing the interactions very carefully and adding the filter on the visual that deactivates the date range when a selection is made on the button slicer. 

I’m happy to provide the formulas I am using in any of my measures if that helps.

Please help!  I don’t want to have to tell my users that they need to “Clear Filters” every time they want to switch from Custom to YTD/PTD.  That seems to be the only order of selections that breaks things. 

[Stefancy]

I have attached a pbix with several approaches, none of which work. I believe the problem is that I am trying to apply a new filter context against that which needs to be filtered for the original calculation to work. 

pbix:  https://drive.google.com/file/d/10E09CQFWoUjBe-Kd04nJYa0_9RtlnLrK/view?usp=sharing

[v-yalanwu-msft]

Hi @Stefancy  ,  

According to your pbix, "Clear Filters" can be realized by “ALL()”in dax, but from the visual object, it can only be manually selected by the filter. So you could change the measure as follows:

Total Revenue PTD =
CALCULATE (
    SUM ( Revenue[Revenue] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date_V2] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 01 )
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date_V2] < TODAY () )
)

Total Revenue YTD =
CALCULATE (
    SUM ( Revenue[Revenue] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date_V2] >= DATE ( YEAR ( TODAY () ), 01, 01 )
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date_V2] < TODAY () )
)

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Stefancy]

I see what the intent is.  It does clear the date filter when switching from a custom date range to YTD, but it breaks the YTD calculation.  Here are screenshots of the data in table form of what YTD is now doing with the original formula right next to it.  

I'll keep researching the ALL() function and see where that takes me.

Thanks!

[Stefancy]

Update

I've been reading about ALL() and decided to try this:

Total Revenue YTD V3 = CALCULATE (
  SUM ( Revenue[Revenue] ),
  FILTER (
    ALL('Calendar'[Date_V2]),
    'Calendar'[Date_V2] >= DATE ( YEAR ( TODAY () ), 01, 01 )
  ),
  FILTER ( ALL('Calendar'[Date_V2]) , 'Calendar'[Date_V2] < TODAY () )
)

This is working exactly the same as the original formula - so it is no longer repeating the total revenue, but it still doesn't clear the custom sub select of dates.

[v-yalanwu-msft]

Hi, @Stefancy ;

Can you  share me with your pbix file after removing sensitive data?

[Stefancy]

Hi @v-yalanwu-msft here is a pbix that reproduces the issue described.  

https://drive.google.com/file/d/1Wf1Qktv8RRvurCTAFmwKGqyOXl3qahgH/view?usp=sharing

[Stefancy]

Hi, I can start working on it.  Unfortunately, everything about it is sensitive, from clients to the data source address, so I'll create some sample data and load locally.  Thanks for the reply! 

[v-yalanwu-msft]

hi, @Stefancy ;

Thank you for your reply. Now that it's working,  so can you share your methods and mark your posts as "Reply". This will help others in the community easily find a solution if they face the same problem as you. Thank you very much.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.