Forum Discussion

Krexx's avatar
Krexx
Helper I
5 years ago
Solved

If function (True/False) based on date slicer

Hello, i would like to generate an dynamic measure based on an date slicer from an date table.

So if the date slicer if from 01/04/2019 until 31/12/2019 i would expect following output:

Date of birthDynamic Measure Output
01.02.2019false
04.03.2019false
06.05.2019true
07.08.2019true
23.12.2019true


Something like this:

TEST =
VAR EndDate = max(Date[Date])
Var Startdate = min(Date[Date])
VAR Result =
    IF(Table[BirthDate] >= Startdate && Table[BirthDate] <= Enddate, True, False)
Return
Result

Thanks in advance
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Krexx ,

    According to my understand , you want to dynamically display True()/False() based on Slicer ,right?

     

    Firstly , you need to create a calendar table using :

    DateSlicer =

CALENDAR ( "2019/1/1", "2019/12/31" )

    Make sure the data type of Date of birth is “Date”

    Then use the following formula:

    Measure =
VAR _maxDate =
    MAX ( 'DateSlicer'[Date] )
VAR _minDate =
    MIN ( 'DateSlicer'[Date] )
RETURN
    IF (
        MAX ( 'DateTable'[Date of birth] ) > _minDate
            && MAX ( 'DateTable'[Date of birth] ) <= _maxDate,
        TRUE (),
        FALSE ()
    )

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Krexx ,

    According to my understand , you want to dynamically display True()/False() based on Slicer ,right?

     

    Firstly , you need to create a calendar table using :

    DateSlicer =

CALENDAR ( "2019/1/1", "2019/12/31" )

    Make sure the data type of Date of birth is “Date”

    Then use the following formula:

    Measure =
VAR _maxDate =
    MAX ( 'DateSlicer'[Date] )
VAR _minDate =
    MIN ( 'DateSlicer'[Date] )
RETURN
    IF (
        MAX ( 'DateTable'[Date of birth] ) > _minDate
            && MAX ( 'DateTable'[Date of birth] ) <= _maxDate,
        TRUE (),
        FALSE ()
    )

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Krexx ,

    Try like this

    count(Table[BirthDate] )+0

     

    As you selected a range as table filter might not go away.

    Create a date table from Mon to max brith dates and join ith with table . And plot this measure with date from date table

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

    Appreciate your Kudos.