# Datediff Measure PQ

Hi DNA Team,

I am trying to calculate the dates difference between two dates in PQ using measure instead of using m-code in the transformation section.

I tried to use DAX but I am getting the below error message:

Hereâ€™s the DAX to do this:

Elapsed DAX =

DATEDIFF(
SELECTEDVALUE( Data[Start Date] ),
SELECTEDVALUE( Data[End Date] ),
DAY
)

I also stumbled on a super simple way to do this in Power Query recently (I used to write a short custom M duration function):

• Brian

@BrianJ Thank you , but I i wrongly interpreted my question, I would like to achieve it in the Excel Power Query Dax formula not in Power BI , selectvalue is not avaibale in PQ in the Excel. I can easily write the formula in the M-Code but I was suprise this morning that I was not able to reflect my figure from Power BI to PQ in the Excel as Dax

Okay, no problem. This works equally well:

Elapsed DAX 2 =

DATEDIFF(
MAX( Data[Start Date] ),
MAX( Data[End Date] ),
DAY
)

• Brian
2 Likes

Thank you @BrianJ

Since a date is a single value DAX is unable to deduct it end fate from the start date, therefore, we always need to use a formula that will convert a date into a single value? Like Max/ MIn in PQ Excel and select value in BI?

Exactly right. If we were just to put Start Date or End Date by themselves into the DAX formula, that would be whatâ€™s referred to as a â€śnaked column referenceâ€ť, which is not allowed in DAX measures because it provides insufficient information to resolve which value of the column is to be used in the calculation â€“ thus we need an additional function, such as Max, Min, etc. to resolve that ambiguity.

Note however, if we did this is a calculated column rather than a measure, we would not need MAX, because the calculated column by nature provides the row context that resolves the naked column ambiguity.

• Brian
1 Like