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 ) )
Jihwan_Kim
2 years agoSuper User
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 )
)