cancel
Showing results for 
Search instead for 
Did you mean: 
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
RicoZhou
Community Support
Community Support

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

3 REPLIES 3
gggar
Helper I
Helper I

Hi @RicoZhou ,

 

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.

RicoZhou
Community Support
Community Support

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

RicoZhou
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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors