CODEWITHSUNDEEP
powerbidaxcalculated-columnswindow-functionsdate-difference
dragonfury2
dragonfury2

Reputation: 428

What is the PowerBI/DAX query equivalent of this SQL windowed function

I have the following Table:

   NDayNo   Customer    Date       CallID
     0          A     02/09/2018    48451
     24         A     26/09/2018    48452
     0          B     21/09/2018    48453
     4          B     25/09/2018    48454
     0          C     17/09/2018    48455
     8          C     25/09/2018    48456
     9          C     26/09/2018    48457
     9          C     26/09/2018    48458
     0          D     09/09/2018    48459

The NDayNo. value was worked out using this function in SQL:

COALESCE(DATEDIFF(day,FIRST_VALUE(Date) OVER (PARTITION BY Customer ORDER By Date),Date),0)

NDayNo. = working out the first time the customer contacts in the month (=0) and then how many days until next time they contact.

Im trying to replicate the same logic in PowerBI. Anybody know how I can calculate this as a calculated column/ DAX query?

Upvotes: 2

Views: 2841

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

This should work for you:

enter image description here

NDayNo =
DATEDIFF (
    CALCULATE ( MIN ( 'table'[Date] ), ALLEXCEPT ( 'table', 'table'[Customer] ) ),
    'table'[Date],
    DAY
)

This DAX expression returns for each row the difference in days between the minimum [date] in the whole table (only filtered to the [Customer] in that row) and the [date] in that row.

Upvotes: 3

Related Questions