Calculate The Difference In Days Between Purchases Or Events In Power BI

2 Likes

This is very helpful!

For my purposes in using this function, I would want to have the difference in days being calculated for each customer instead of solely looking at the different between last purchase date. How could that be accomplished? Can you provide an example of that logic?

Thank you

1 Like

@jmwdba,

I had the exact same requirement. Here’s how I adapted the measure that Sam provided in my report (my HrActionID field is the equivalent of a CustomerID).

I hope this is helpful.

    DaysBetweenProcessSteps = 
    VAR FirstOverallDate =
        CALCULATE (
            MIN ( FactHrActionsStatus[ProcessStepCompletionDate] ),
            ALLEXCEPT ( FactHrActionsStatus, FactHrActionsStatus[HrActionID] )
        )
    VAR IndexNum =
        MAX ( FactHrActionsStatus[ProcessStepIndex] )
    VAR PreviousIndexNum =
        CALCULATE (
            MAX ( FactHrActionsStatus[ProcessStepIndex] ),
            FILTER (
                ALLSELECTED ( FactHrActionsStatus ),
                FactHrActionsStatus[ProcessStepIndex] < IndexNum
            )
        )
    VAR CurrentDate =
        VALUE ( ( SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ) ) )
    VAR PriorDate =
        VALUE (
            (
                CALCULATE (
                    SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ),
                    FILTER (
                        ALL ( FactHrActionsStatus ),
                        FactHrActionsStatus[ProcessStepIndex] = PreviousIndexNum
                    )
                )
            )
        )
    RETURN
        CALCULATE (
            IF (
                MIN ( FactHrActionsStatus[ProcessStepCompletionDate] ) = FirstOverallDate,
                1,
                IF (
                    SELECTEDVALUE ( FactHrActionsStatus[ProcessStepCompletionDate] ) = BLANK (),
                    0,
                    CurrentDate - PriorDate
                )
            ),
            FactHrActionsStatus[HrActionID]
        )
1 Like

Hi @BrianJ ,

Thanks for your reply. So I tried using this method, but the column only returned two values for the entire dataset: 1 and -43983.

What I am trying to accomplish is figuring the days difference between a student’s last enrolled term and their current term. For example, if a student’s last enrolled term was in 1/5/2018 and their current enrolled term is 8/6/19, I want to have the days between calculated.

Here’s how I set up my calculated column that resulted in the “1” and “-43983” values. Perhaps I missed something…

Days Since Last Enrolled Term = 
VAR FirstOverallDate = CALCULATE(
                            MIN('Days Difference in Last Enroll Term'[Term Begin Date]),
                            ALLEXCEPT('Days Difference in Last Enroll Term','Days Difference in Last Enroll Term'[Student ID])
)
VAR IndexNumber = MAX('Days Difference in Last Enroll Term'[Index])
VAR PreviousIndexNumber = CALCULATE(
                            MAX('Days Difference in Last Enroll Term'[Index]),
                            FILTER(
                                ALLSELECTED('Days Difference in Last Enroll Term'),
                                'Days Difference in Last Enroll Term'[Index] < IndexNumber)
)
VAR CurrentDate = VALUE((SELECTEDVALUE('Days Difference in Last Enroll Term'[Term Begin Date])))
VAR PriorDate = VALUE(
                    (CALCULATE(
                        SELECTEDVALUE('Days Difference in Last Enroll Term'[Term Begin Date]),
                        FILTER(
                            ALL('Days Difference in Last Enroll Term'),
                            'Days Difference in Last Enroll Term'[Index] = PreviousIndexNumber)
                    )
                    )
)
RETURN
CALCULATE(
    IF(
        MIN('Days Difference in Last Enroll Term'[Term Begin Date]) = FirstOverallDate,
        1,
        IF(
            SELECTEDVALUE('Days Difference in Last Enroll Term'[Term Begin Date]) = BLANK(),
            0,
            CurrentDate - PriorDate
        )
    ),
    'Days Difference in Last Enroll Term'[Student ID]
)

@jmwdba,

Have you tried it as a measure, rather than a calculated column?

It was written as the former, and will produce very different results due to the difference in evaluation context between measures and calculated columns.

I just tried the DAX in my earlier post as a calculated column in my model, and it produces nothing but zeros, ones and blanks, but works perfectly as a measure.

Thank you so much!!! I had the very same question. Using this to calculate RFM