Forum Discussion

ajay_gajree's avatar
ajay_gajree
Helper I
6 years ago
Solved

Dates Between Flag

Hi

I am trying to create a Flag Column to tag a date column as 1 or 0 depending if it falls between 2 dates

I have tried

DateFlag =
IF(
DATESBETWEEN( Data [Date],
DATE(2020,01,15),
DATE(2020,02,14),
1,

0
)

This is giving me the error message

A table of multiple values was suppler where a single value was expected

Any help appreciated!
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    For your issue, it is because DATESBETWEEN returns a range of date but not a single value.

    So please try this calculated column:

    Column = IF('Table'[Date] in DATESBETWEEN('Table'[Date],DATE(2020,1,15),DATE(2020,2,14)),1,0)

    The result shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

4 Replies

  • ajay_gajree's avatar
    ajay_gajree
    Helper I
    6 years ago
    I want to create this flag for every row in my table so it is a column
    • Gordonlilj's avatar
      Gordonlilj
      Solution Sage
      6 years ago

      You could try and use FIRSTNONBLANK in your calculation like below

      DateFlag = 
IF(CALCULATE(FIRSTNONBLANK('Table'[date],'Table'[date]),
DATESBETWEEN( 'Table'[date],
DATE(2020,01,15),
DATE(2020,02,14))),
1,
0
)

      Or without using DATESBETWEEN

      DateFlag = 
IF(
    'Table'[date] >= DATE(2020,01,15) && 
    'Table'[date] <= DATE(2020,02,14),1,0
  )

       

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    For your issue, it is because DATESBETWEEN returns a range of date but not a single value.

    So please try this calculated column:

    Column = IF('Table'[Date] in DATESBETWEEN('Table'[Date],DATE(2020,1,15),DATE(2020,2,14)),1,0)

    The result shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi