max between measure and date field

[Anonymous]

Hi,

I have a booking table that has bookings wiht start and end dates.  I also have two measure columns that pulls through the max and min values of a slicer on the page.

I am then trying to calculate the MIN between a bookings end date and the slicers max date. 

The measure for the slicer has a code of:

mindate = DATEVALUE((FORMAT(MIN(ssam_start_dates[date]),"dd/mm/yyyy")))

However if i create a column i get an error:

Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models.

If i create a measure i get an error:

A single value for column 'Occupancy End' in table 'Booking SessionBedroomBedBooking' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

many thanks,

[v-jiascu-msft]

Hi @Anonymous,

What does your data look like? Try this formula, please.

calenddate =
MIN (
    'Booking SessionBedroomBedBooking'[mindate],
    MIN ( 'Booking SessionBedroomBedBooking'[Occupancy End] )
)

Can you use this formula directly?

mindate =
DATEVALUE ( MIN ( ssam_start_dates[date] ) )

Best Regards,
Dale

[Anonymous]

Hi,

The data is very simple table from a SQL database,

I also have a date table that is powering the slicer, the table is called ssam_start_dates and i am using the date column

If I create a Columns field I get:

If I create a measure I get:

What i am trying to do is find :

the MIN between the Max slicer value and the End Date

the MAX between the Min slicer value and the  Start Date.

Thanks,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Since you'd like to compare the dates with the slicer, you need a measure. Please refer to the two measures below. Both of them work.

Measure =
MIN (
    MAX ( 'ssam_start_dates'[Date] ),
    MIN ( 'Booking SessionBedroomBedBooking'[occupancy end] )
)

Measure 2 =
MAX (
    MIN ( 'ssam_start_dates'[Date] ),
    MIN ( 'Booking SessionBedroomBedBooking'[occupancy start] )
)

Best Regards,
Dale