Datediff Measure PQ

DAX DAX Calculations
1

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:

image
image1762Ă—867 50 KB

Could please advise?date.xlsx (102.7 KB)

I will appreciate your help.

2

@Matty,

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):

image
image1120Ă—422 24.3 KB

image

I hope this is helpful.

  • Brian
3

@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 :frowning: , 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 :open_mouth:

4

@Matty,

Okay, no problem. This works equally well:

Elapsed DAX 2 = 

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

image

  • Brian
2 Likes
5

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?

6

@Matty,

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