Forum Discussion
Dimi_2207
2 years agoHelper I
Serial number based on condition in DAX
Hello everyone! Could, you, please, help me with the following task in DAX. I am attaching sample in excel. I have the following columns: 1) "Date" - Call date 2) Customer ID (can be custo...
- 2 years ago
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
OFFSET function (DAX) - DAX | Microsoft Learn
Repeated call in 7 days? CC = VAR _currentdate = Data[Date] VAR _prevdate = SUMMARIZE ( OFFSET ( -1, Data, ORDERBY ( Data[Date], ASC ), , PARTITIONBY ( Data[Customer] ), MATCHBY ( Data[Date], Data[Customer] ) ), Data[Date] ) RETURN SWITCH ( TRUE (), _prevdate = BLANK (), "N", _currentdate - _prevdate > 7, "N", _currentdate - _prevdate <= 7, "Y" )EARLIER function (DAX) - DAX | Microsoft Learn
Call number CC = VAR _startNdate = MAXX ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) && Data[Repeated call in 7 days? CC] = "N" && Data[Date] <= EARLIER ( Data[Date] ) ), Data[Date] ) VAR _nextNdate = MINX ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) && Data[Repeated call in 7 days? CC] = "N" && Data[Date] > EARLIER ( Data[Date] ) ), Data[Date] ) VAR _tnextNdatenotblank = FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) && Data[Date] >= _startNdate && Data[Date] <= _nextNdate && Data[Date] <= EARLIER ( Data[Date] ) ) VAR _tnextNdateblank = FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) && Data[Date] >= _startNdate && Data[Date] <= EARLIER ( Data[Date] ) ) RETURN SWITCH ( TRUE (), _nextNdate <> BLANK (), COUNTROWS ( _tnextNdatenotblank ), COUNTROWS ( _tnextNdateblank ) )
Dimi_2207
2 years agoHelper I
Thank you very much ! This is exactly what i was looking for !