How to convert SQL windowed function to DAX

[Anonymous]

I have this table:

NormalisedDayNo	Customer	Date
0	A	02/09/2018
24	A	26/09/2018
0	B	21/09/2018
4	B	25/09/2018
0	C	17/09/2018
8	C	25/09/2018
9	C	26/09/2018
9	C	26/09/2018
0	D	09/09/2018

In SQL worked out the NDayNo. by doing following calc

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

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

How do I calculate this as a DAX query?

[AkhilAshok]

If you just want a measure, you could do somethng like this:

NDayNo = 
VAR CurrentCustomer =
    MAX ( 'Table'[Customer] )
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR FirstDayofCustomer =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        'Table'[Customer] = CurrentCustomer,
        ALL ( 'Table')
    )
RETURN
    DATEDIFF ( FirstDayofCustomer, CurrentDate, DAY )

[Anonymous]

Thanks, Is there anyway to calculate this as a calc column?

Reason being I need to store the value at row level, and for it to dynamically change based on filters applied.

[AkhilAshok · Accepted solution]

You can create the below calculated column:

NDayNo. =
VAR CurrentCustomer = 'Table'[Customer]
VAR CurrentDate = 'Table'[Date]
VAR FirstDayofCustomer =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        'Table'[Customer] = CurrentCustomer,
        ALL ( 'Table' )
    )
RETURN
    DATEDIFF ( FirstDayofCustomer, CurrentDate, DAY )