Using Date Range from a Slicer in a DAX Query PowerBI

Question

I have a date slicer that is controlled by the user. I am trying to use the users min/max date selection as a indicator. I have created two measures - one for the min value and another measure for the max value. Please see DAX code for one below:

NewMin = CALCULATE(FIRSTDATE('Master Query'[RegisterDate]),ALLSELECTED('Master Query'))

Now, on the Master Query Table there is a column that holds date values in the format of dd/mm/yyyy 00:00:00...I am adding another column and using a if statement to get a 0/1 output (i.e. checking if the date column is between the min and max date slicer selection) but it is not working. See DAX Below:

RangeCheck = IF('Master Query'[RegisterDate] >= 'Master Query'[NewMin] && 'Master Query'[RegisterDate] <= 'Master Query'[NewMax],1,0)

This does not work and I am unsure as to why. It seems its not recognising the dates or cant decipher if date is between the two min and max boundries.

KarthikBhyresh-MT KarthikBhyresh-MT · Accepted Answer · 2021-06-14T18:43:05

Using LASTDATE to retrieve the value of the last visible date in a time period returns a table containing that last date – not only the date.

It does not return a date. It returns a table, containing the date.

The reason for this is that LASTDATE is a time intelligence function. Its main purpose is to be used as a filter argument in CALCULATE. CALCULATE filter arguments are tables. Therefore, for a function to be used in a measure, it needs to return a table.

A better way to express the previous calculation is by using scalar functions, like MIN instead of FIRSTDATE and MAX instead of LASTDATE. MIN and MAX do not return tables: they return the values of the first and last dates.

In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.
There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

If you have followed all the above you can try something like this (Since the date slicer would have already filtered the table)

Measure:

InBetween = 
VAR maxValue = CALCULATE ( MAX ( Table[column] ), ALLSELECTED ( Table) )
    
VAR minValue = CALCULATE ( MIN ( Table[column] ), ALLSELECTED ( Table) )

RETURN IF ( minValue <= Master Query[RegisterDate] <= maxValue, 1, 0 )

Alternately you can use many other DAX expressions to generate a set of dates in the period (min,max) and evaluate main column against it. Make sure all are in datetime format or in date and not in text.

Using Date Range from a Slicer in a DAX Query PowerBI

2 Answers