slicer made from firstdate and last date of 2 tables

[misen13]

Hi,

I'm trying to create a date slicer from 2 tables. I'd like to have the firstdate and the most recent dates between the 2 tables.

This is the measure I've tried to use, but I'm getting the error:

A table of multiple values was supplied where a single value was expected.

PeriodeConfirmationAffectation = DATESBETWEEN('Calendar'[Date], if(FIRSTDATE(Affectation[Date]) < FIRSTDATE(Confirmation[Date]), FIRSTDATE(Affectation[Date]), FIRSTDATE(Affectation[Date])), if(LASTDATE(Affectation[Date]) < LASTDATE(Confirmation[Date]), LASTDATE(Affectation[Date]), LASTDATE(Affectation[Date])))

Why is this happening?

Thanks.

Data file: https://ufile.io/muw3w

*Calendar is a date table ranged from 01/01/2000 to 31/12/2050

[v-lili6-msft · Accepted solution]

hi, @misen13

DATESBETWEEN Function Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

So you couldn't use it to create a measure

After my test, I found that You have a problem with the logic of the formula, if it is like this

Table = 
DATESBETWEEN (
    'Calendar'[Date],
    IF (
        FIRSTDATE ( Affectation[Date] ) < FIRSTDATE ( Confirmation[Date] ),
        FIRSTDATE ( Affectation[Date] ),
        FIRSTDATE (Confirmation[Date] )
    ),
    IF (
        LASTDATE ( Affectation[Date] ) < LASTDATE ( Confirmation[Date] ),
        LASTDATE (  Confirmation[Date] ),
        LASTDATE ( Affectation[Date] )
    )
)

Then it returns a date table from min date of Affectation table and Confirmation table to max date of Affectation table and Confirmation table

for this sample data, it returns from "2017-03-01" to "2019-03-05"

then create the relationship between this date table with Calendar table like below:

Then drag the field date from new date table into slicer.

Best Regards,

Lin

[misen13]

Thanks! You're awesome!