Best way to manage multiple dates

[Anonymous]

I am wondering what is the best way to mange multiple dates in a single table. For example, our leads get date stamped when the transition stages. So there are 4 or 5 dates on a lead record, in addition to others like a created date. I have a Calendar table and it is related to the lead table by the created date. I would like to do some other measurements in this file, based on state transition dates, but it seems like I need to duplicate tables or do this in another file completely as the calendar and lead tables are already connected. Is there a better way to do this?

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You can join all of your dates into the date table although only one of the connections will be the primary active one.

In my example below my primary join is on [Paid Date] with the other 4 being on [Entry Date], [Processed Date], [Received Date], and [Service Date]:

If I have a measure that calculates "Paid Amount":

Paid Amount = SUM ( vCLAIM[Paid] )

I can write another measure that will use that first measure but switches to use vCLAIM[Service Date] > DATE[Date] instead:

Paid Amount Service Date = 
CALCULATE(
    [Paid Amount],
    USERELATIONSHIP( vCLAIM[Service Date], DATES[Date] )
)

You can go a step further if you add a table of date "selections" the feed that into a measure.

This DAX will create a table in my model that I can use in another measure to switch the dates using a slicer.

Date Selction =
DATATABLE (
    "Date Type", STRING,
    "Order", INTEGER,
    {
        { "Service Date", 1 },
        { "Received Date", 2 },
        { "Entry Date", 3 },
        { "Processed Date", 4 },
        { "Paid Date", 5 }
    }
)

Date Type	Order
Service Date	1
Received Date	2
Entry Date	3
Processed Date	4
Paid Date	5

Then I can add my [Date Type] field to a slicer and the selection to a measure like so.  If no [Date Type] is selected is uses the [Paid Date] field:

Paid Amount with Date Selection:= 
VAR DateType =
    SELECTEDVALUE ( 'Date Selection'[Date Type], "Paid Date" )
RETURN
    SWITCH (
        TRUE (),
        DateType = "Paid Date", CALCULATE (
            SUM ( vCLAIM[Paid] ),
            USERELATIONSHIP ( vCLAIM[Paid Date], DATES[Date] )
        ),
        DateType = "Service Date", CALCULATE (
            SUM ( vCLAIM[Paid] ),
            USERELATIONSHIP ( vCLAIM[Service Date], DATES[Date] )
        ),
        DateType = "Received Date", CALCULATE (
            SUM ( vCLAIM[Paid] ),
            USERELATIONSHIP ( vCLAIM[Received Date], DATES[Date] )
        ),
        DateType = "Entry Date", CALCULATE (
            SUM ( vCLAIM[Paid] ),
            USERELATIONSHIP ( vCLAIM[Entry Date], DATES[Date] )
        ),
        DateType = "Processed Date", CALCULATE (
            SUM ( vCLAIM[Paid] ),
            USERELATIONSHIP ( vCLAIM[Processed Date], DATES[Date] )
        ),
        SUM ( vCLAIM[Paid] )
    )

You can even pull in the 'Date Selection'[Date Type] field into a visual along with your switching mesure and it will calc:

[Anonymous]

Oh wow - very cool. Thank you for the explanation. The use relationship and date table will help me accomplish what I need.

[mattbrice]

You need to look into using the USERELATIONSHIP or CROSSFILTER functions to use a different relationship between tables.   Only one relationship between two tables can be active, but you can have many inactive relationships between the two tables and use the functions above to explicitly use one of them in a measure.