Date Grouping Logic in DAX by date slicer

[matthewkaess]

Apologies in advance as this is somewhat complex so I'm try to provide as much detail as I can. Then again, maybe not for someone much smarter than I am... 🙂 If only the StartDate and EndDates are in the table, the result is the earliest and last date for the object. Not the in between dates. When I add the StartDate and EndDates from the table, it all falls into place.

The basic premise of this issue is using IF or SWITCH logic to modify Start and End dates based on the dates selected in a date slicer. It's two tables, the fact table containing the dates and their corresponding object numbers and a date table. So there are four dates; 

• MinDateInContext (slicer)
• MaxDateInContext (slicer)
• StartDate (table)
• EndDate (table)

The dates in the slicer are 04/01/2021 to 31/01/2021. Those constitute the Min Date in Context and Max Date in Context. There are a few combinations of logic that need to be applied. The idea is to return either the StartDate or MinDateInContext, EndDate or MaxDateInContext based on how the two interact wiht the slicer.

EndDate Logic

• IF EndDate == BLANK(), MaxDateInContext
• IF EndDate < MinDateInContext, BLANK()

StartDate Logic

• IF StartDate < MinDateInContext && EndDate == BLANK(), MinDateInContext
• IF StartDate > MinDateInContext, StartDate

Measures

Min Date In Context =

CALCULATE(

MIN( 'Dates'[Date] ),

ALLSELECTED( 'Dates'[Date] )

)

Max Date In Context =

CALCULATE(

MAX( 'Dates'[Date] ),

ALLSELECTED( 'Dates'[Date] )

)

Start Date =

VAR MinDate = [Min Date In Context]

VAR MaxDate = [Max Date In Context]

VAR StartDate =

CALCULATE(

FIRSTDATE( 'Object Billing Export'[Start Date] ),

ALL( Dates )

)

VAR EndDate = CALCULATE(

LASTDATE( 'Object Billing Export'[End Date] ),

ALL( Dates )

)

RETURN

SWITCH(

TRUE(),

StartDate > MinDate && StartDate < MaxDate, StartDate,

StartDate < MinDate && ISBLANK( EndDate ), MinDate,

EndDate > MinDate, MinDate

)

End Date =

VAR MinDate = [Min DATE IN Context]

VAR MaxDate = [Max DATE IN Context]

VAR StartDate =

CALCULATE(

FIRSTDATE( 'Object Billing Export'[Start Date] ),

ALL( Dates )

)

VAR EndDate = CALCULATE(

LASTDATE( 'Object Billing Export'[End Date] ),

ALL( Dates )

)

RETURN

SWITCH(

TRUE(),

ISBLANK( EndDate ) && StartDate < MaxDate, MaxDate,

EndDate >= MinDate && EndDate <= MaxDate, EndDate,

StartDate > MinDate && EndDate > MaxDate, MaxDate,

EndDate < MinDate, DATEVALUE( BLANK() )

)

[matthewkaess]

Sorry wrong image - needs table headers.

With table dates

Without table dates