Date difference

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.