cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Serial number based on condition in DAX

Hello everyone!

I am attaching sample in excel.

I have the following columns:

1) "Date" - Call date

2) Customer ID (can be customer phone number)

3) "Repeated calls in 7 days?" - calculated columnt that puts "Y" in row if this is a repeated call (there was a call from the same "Customer ID" within period of 7 days before "Date") , if not - it puts "N".

4) Call number - this is the calculated column i am struggling with..I'd like to assign consecutive number for each call if it happens from the same customer ID within the period of 7 days. If the previous call from id happened earlier - the count starts again from "1".

 Date Customer Id Repeated calls in 7  Days ? Call number 24-11-23 1111111 N 1 25-12-23 1111111 N 1 30-12-22 1111111 Y 2 06-01-24 1111111 Y 3 02-02-24 1111111 N 1 04-02-24 1111111 Y 2 09-02-24 1111111 Y 3

Your help will be much appreciated !

1 ACCEPTED SOLUTION
Super 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 )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Frequent Visitor

Thank you very much ! This is exactly what i was looking for !

Super 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 )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors