How to create a DAX measure to find time between order dates

[Anonymous]

Hi, I'm looking to create a DAX measure to solve the same problem as this:

https://community.powerbi.com/t5/Desktop/Date-difference-between-values-in-same-column/m-p/454941#M2...

The solution to this was creating a calculated column, however I am limited to using only DAX measures in my current model.

Basically, given a list of companies and order dates, I need a field that tells me how long ago the last order was for every row, like:

Thanks in advance for the help!

[v-chenwuz-msft]

Hi @Anonymous 

Try this measure:

Measure =
VAR _top2 =
    TOPN(
        2,
        FILTER(
            ALL( 'Table' ),
            [Company] = SELECTEDVALUE( 'Table'[Company] )
                && [Order Date] < SELECTEDVALUE( 'Table'[Order Date] )
        ),
        [Order Date], DESC
    )
VAR _pdate =
    MAXX( _top2, [Order Date] )
RETURN
    DATEDIFF( _pdate, SELECTEDVALUE( 'Table'[Order Date] ), DAY )

And the pbix file I put in the attachment you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

[Anonymous]

Thanks for the reply! PBI won't let me use my company column in this measure. Both the company column and the order date column are just regular columns in my model, not measures, which I believe is the problem. Is there a workaround to this that you know of?