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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gggar
Helper I
Helper I

Get date slicer "start" and "end" values

Hello,

 

I have a question.

 

Final Objective

To warn users if they select dates beyond the available date range in my table. The idea is to capture date slicer "start" and "end" values to determine if users select dates beyond the available date range scope.

 

Table

Basic calendar table from 1-Jan-21 to TODAY()

 

DAX

 

Date Harvest Min = 
    CALCULATE(
        MIN( '. Master Cal'[Date] ),
        ALLSELECTED( '. Master Cal'[Date] )
    )

 

 

 

Date Harvest Max = 
    CALCULATE(
        MAX( '. Master Cal'[Date] ),
        ALLSELECTED( '. Master Cal'[Date] )
    )

 

 

Use case

In date picker, select 1-Nov-10 as start value and 20-May-31 as end value.

Screenshot 2021-05-11 231122.png

Those DAX return 01/01/21 and 11/05/21, which are the start and end dates of my table (the min and max dates).

I want to have DAXs that return 1-Nov-10 and 20-May-31.

 

Anyone knows the answer for the correct DAX? Or any answers, that can achieve the final objective, whatever the approach is.

Any help will be greatly appreciated! Thank you.

1 ACCEPTED SOLUTION

Hi @gggar 

 

If you just choose data out of your range in your slicer(between), PBI won't show an error message or any response to you. It will only show default max date and default min date in your data model.

But you can create a measure to get some message warning.

Firstly, we need to create a calendar table with a large enough range. (Because the date you choose should be in data model.) Then we build a slicer by date column in this table.

Slicer = CALENDAR(DATE(2000,1,1),DATE(2100,12,31))

Relate Slicer table and .Master Cal table by Date column.

Then build a measure as below.

Warning = 
VAR _End =
    MAX ( Slicer[Date] )
VAR _Start =
    MIN ( Slicer[Date] )
VAR _Range =
    VALUES ( '. Master Cal'[Date] )
VAR _Result =
    SWITCH (
        TRUE (),
        _Start
            IN _Range
            && NOT ( _End IN _Range ), "Warning : End out of Range",
        NOT ( _Start IN _Range )
            && _End IN _Range, "Warning : Start out of Range",
        _Start
            IN _Range
            && _End IN _Range, "All in Range",
        "Warning : All out of Range"
    )
RETURN
    _Result

Result is as below.

If you select 1-Nov-10 as start value and 20-May-31 as end value.

It will show a warning to you. You can change the text in measure to show the warning you want.

1.png

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

4 REPLIES 4
Analitika
Post Prodigy
Post Prodigy

Hello I have a problem when I want filter calculations according to end date of slicer. I used 

filter('Date', 'Date'[Date] =maxx(allselected('Date') , 'Date'[Date])) but it didn't help me.
gggar
Helper I
Helper I

Hi @v-rzhou-msft ,

 

Thank you for the answer.

Yes am aware of that. Thanks.

 

I guess there's no way PBI can show an error message or some response if out of range dates are chosen.

Hi @gggar 

 

If you just choose data out of your range in your slicer(between), PBI won't show an error message or any response to you. It will only show default max date and default min date in your data model.

But you can create a measure to get some message warning.

Firstly, we need to create a calendar table with a large enough range. (Because the date you choose should be in data model.) Then we build a slicer by date column in this table.

Slicer = CALENDAR(DATE(2000,1,1),DATE(2100,12,31))

Relate Slicer table and .Master Cal table by Date column.

Then build a measure as below.

Warning = 
VAR _End =
    MAX ( Slicer[Date] )
VAR _Start =
    MIN ( Slicer[Date] )
VAR _Range =
    VALUES ( '. Master Cal'[Date] )
VAR _Result =
    SWITCH (
        TRUE (),
        _Start
            IN _Range
            && NOT ( _End IN _Range ), "Warning : End out of Range",
        NOT ( _Start IN _Range )
            && _End IN _Range, "Warning : Start out of Range",
        _Start
            IN _Range
            && _End IN _Range, "All in Range",
        "Warning : All out of Range"
    )
RETURN
    _Result

Result is as below.

If you select 1-Nov-10 as start value and 20-May-31 as end value.

It will show a warning to you. You can change the text in measure to show the warning you want.

1.png

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

v-rzhou-msft
Community Support
Community Support

Hi @gggar 

Your calendar table is from 1-Jan-21 to TODAY().

. Master Cal = CALENDAR(DATE(2021,01,01),TODAY())

So in your data model, there are only values between 2021/01/01 to today.

I think you should use .Master Cal[Date] to build a Slicer( choose Between) as a Date picker.

1.png

You see the slicer will show 2020/01/01 to 2021/05/13(today) by default.

In your data model, 2021/01/01 is the min date and today is the max date.

So you change your start date to 2010/11/01 and end date to 2031/05/20, you will only get the min and max date in your data model (2020/01/01 and 2021/05/13 )

You couldn't get 2010/11/01 and 2031/05/20 because they are not in your data model.

2.png

If you want to get 2010/11/01 and 2031/05/20 by min and max function in Dax, you should build a calendar table contains them.

. Master Cal = CALENDAR(DATE(2010,11,01),DATE(2031,05,20))

And you can remind your user, if the [Date Harvest Min] and [Date Harvest Max] will only show default min or default max date which are different from the start date or end date they select in Date picker, it means they have choosen dates out of your calender table range.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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