2

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?

dragonfury2
  • 375
  • 5
  • 20

1 Answers1

3

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.

Marco Vos
  • 2,888
  • 1
  • 9
  • 10
  • For this; DATEDIFF( CALCULATE(MIN('table'[Date],ALLEXCEPT('table','table'[customer])),'table'[Date],DAY) Im getting 'Too few arguments passed to DATEDIFF, min requirement is 3' error. Is there some extra code that's been snipped off your screenshot? Thanks – dragonfury2 Oct 28 '18 at 11:22
  • You forgot the closing bracket after `MIN('table'[Date]` . I will add the code so it can be copy/pasted. – Marco Vos Oct 28 '18 at 12:03
  • Thanks. What if I want to filter the results? for example if there is a FLAG column next to it with values [1] or [0], and I filter out all the [0]s, leaving me with just [1]s. Will this calculated column adjust the NDayNo? So from the above table, if I was to filter out the top row (CallID. 48451), the NDayNo for CallID. 48452 should adjust to 0. – dragonfury2 Oct 28 '18 at 14:10
  • No, it will not recalculate when you filter the table. This is a calculated column and not a maesure. – Marco Vos Oct 28 '18 at 14:51
  • I see, in this case how would I convert this into a measure? Ensuring the value is recalculated every-time a filter is applied on the table? – dragonfury2 Oct 28 '18 at 17:01