How to make "Between" Date Filter go to last date automatically?

[JustinDoh1]

Hi PBI community,

I have posted my PBI file here.

I have posted this question back in 2021, but I was not able figure this one out.

Basically, I am trying to display Line Chart visual show all the way to the latest day of data available.

So, if I data gets refreshed today, both Line Chart and "Between" Date filter should display all the way to the last date of data.

But, it does not work as expected.

I was hoping, by using a date filter as below, it would help for Date filter point to yesterday.

     Calendar = CALENDAR ( DATE ( 2024, 8, 15 ), Today() -1 )

Somebody mentioned that by applying a Sync slicers, it will do the magic, but it did not do any good either.

What is solution to make the Line Chart show to the last date available on data?

Would different type of Date filter do the magic?

[gmsamborn]

Hi @JustinDoh1 

You could add a calculated column to your calendar table and use it to filter your date slicer (or your visual if required).

_Include = 
VAR _CurrDt = [Date]
VAR _MinDt =
    CALCULATE(
        MIN( 'FactTable'[Date] ),
        ALL( 'FactTable'[Date] )
    )
VAR _MaxDt =
    CALCULATE(
        MAX( 'FactTable'[Date] ),
        ALL( 'FactTable'[Date] )
    )
RETURN
    IF(
        _CurrDt >= _MinDt
            && _CurrDt <= _MaxDt,
        1
    )

Let me know if you have any questions.

[JustinDoh1]

@gmsamborn 

Thank you.

Sorry, how do I handle this:  [Date]?

[gmsamborn]

If you are adding that as a calculated column in your calendar table (not a measure), it won't raise an error.

[JustinDoh1]

@gmsamborn 

Thank you for your correction.

I tried the filter several time, but still the date filter would not adjust to cover to the max date as expected. Can you possibly use the updated PBIX file that I shared on the link please?

[gmsamborn]

Hi @JustinDoh1 

My mistake. It won't automatically update the range of your slicer.

Instead if you change the end date using the slider, and then change it again, the max slicer date *should* be the max date of data.  (I hope that made sense.)

I can't think of another way.

Again, my apologies.

[JustinDoh1]

@gmsamborn 

So, there is no way to make this work?

I have other report (my co-worker's) that seems to be working ("Between" date range), but it is not tied to Line Chart visual, but to a Bar Graph and Matrix.

[gmsamborn]

Hi @JustinDoh1 

Check your IMs for an example.

[JustinDoh1]

@gmsamborn I will trim (deleting all unrelevant data and visuals) his report, and share it for an example in next few days. I will keep you updated. 

[v-linhuizh-msft]

Thanks for the reply from gmsamborn.

Hi @JustinDoh1  , 

Did you remove unrelevant data from your coworker's report, or have you found a solution. If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

[JustinDoh1 · Accepted solution]

@v-linhuizh-msft 

Thank you for following up.

The way it worked was:

1) I just used the table that I initially wanted to create as a date slicer:

Calendar = CALENDAR ( DATE ( 2020, 1, 1 ),Today() -1 )

& created a relationship

2) Refreshed all data before I publish it.

3) I applied Sync slicers to the data slicer:

and it worked magically.