Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
JustinDoh1
Post Prodigy
Post Prodigy

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

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.

 

JustinDoh1_2-1725052425896.png

 

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.
 
JustinDoh1_3-1725052637004.png

 

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?

 

1 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:
JustinDoh1_0-1728509035367.png

and it worked magically.

View solution in original post

10 REPLIES 10
gmsamborn
Super User
Super User

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.

@gmsamborn 

Thank you.

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

JustinDoh1_0-1725054730319.png

 

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

@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?

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.

 

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

Hi @JustinDoh1 

 

Check your IMs for an example.

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

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

@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:
JustinDoh1_0-1728509035367.png

and it worked magically.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.