Date difference

[ArtoKukkonen]

Hi,

could someone have a solution to this how to calculate the difference between dates in the dax according to the example below?

CustomerID	TransactionDate	StatusOn	StatusOff	Diff
100	8.4.2009		12.4.2021
100	26.3.2015	12.10.2021
100	1.11.2015		22.3.2022	161	(22.3.2022-12.10.2021)
100	3.9.2019	13.4.2022
100	12.10.2020		7.2.2023	300	(7.2.2023-13.4.2022)

[_elbpower · Accepted solution]

To calculate the duration in days between consecutive "Status On" and "Status Off" dates for the given dataset, you can follow these steps:

1. Create a new calculated column to combine "Status On" and "Status Off" dates into one column, and another column to indicate whether it's a "Status On" or "Status Off" date.
   
   

CombinedDate = 
IF(ISBLANK([StatusOn]), [StatusOff], [StatusOn])

EventType = 
IF(ISBLANK([StatusOn]), "Status Off", "Status On")
​

1. Sort the table by "TransactionDate" and "CombinedDate" in ascending order.

2. Create a new calculated column to calculate the duration between consecutive "Status On" and "Status Off" events.

DurationInDays =
VAR CurrentRowDate = [CombinedDate]
VAR CurrentRowEventType = [EventType]
VAR PreviousRowDate =
    CALCULATE(
        MAX([CombinedDate]),
        FILTER(
            ALL('YourTable'),
            'YourTable'[TransactionDate] < EARLIER('YourTable'[TransactionDate])
            && 'YourTable'[CustomerID] = EARLIER('YourTable'[CustomerID])
        )
    )
RETURN
    IF(
        CurrentRowEventType = "Status Off" && PreviousRowDate <> BLANK(),
        CurrentRowDate - PreviousRowDate,
        BLANK()
    )

• Replace YourTable with the actual name of your table.

  This calculated column calculates the duration in days between consecutive "Status On" and "Status Off" events for each customer based on the sorted "TransactionDate."

• Now, you have a new column, "DurationInDays," that represents the duration in days between "Status On" and "Status Off" events for each customer.