cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX formula for consecutive events with some criteria

Hi folks,

I am struggling with the code to produce information on consecutive events. The columns below in white are what is in my dataset, the columns in grey are the ones I would like. The logic is as follows. For any given row I would like to understand how many days in a row has that quantity been bought by that customer (customer, product, quantity). As well I would like to understand regardless of the quantity bought, how many days the customer has bought that product (quantity and product). Any help would be greatly appreciated. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: DAX formula for consecutive events with some criteria

Hey @tredeuce ,

i guess you owe @mwegener  a drink, as he prepared a pbix based on your inital screenshot, that did not match the data that you finally provided.

Nevertheless, thank you for providing sample data.

Here is my solution.

First I created a calendar table using this DAX statement:

``````Calendar =
var YearMin = YEAR(MIN('Sample Data Clean Test Names'[FillDate]))
var YearMax = YEAR(MAX('Sample Data Clean Test Names'[FillDate]))
var DateStart = DATE(YearMin , 1 , 1)
var DateEnd = DATE(YearMax , 12 , 31)
return
CALENDAR(DateStart , DateEnd)
, "RunningDayIndex" , DATEDIFF(DateStart , [Date] , DAY) + 1
)``````

Please be aware that the column RunningDayIndex is not used, but as more complex "sequence math" works better using integers creating integer columns inside my calendar table has become a habit.

Next I created a simple measure, I use this measure to discover/flag a missing value, as this resets the sequence. For this I used the column "DispensedQuantity", the measure looks like this:

``````_NullCheck =
var _test = CALCULATE(SUM('Sample Data Clean Test Names'[DispensedQuantity]))
return
IF(ISBLANK(_test) , 1 , BLANK())``````

The measure that counts the number of consecutive days per patientname, drugname, drugstrength looks like this:

``````consecutive days v1 =
var t =
SUMMARIZE(
'Sample Data Clean Test Names'
, 'Sample Data Clean Test Names'[PatientName]
, 'Sample Data Clean Test Names'[DrugName]
, 'Sample Data Clean Test Names'[DrugStrength]
, 'Calendar'[Date]
)
, "dispensedQuantity", CALCULATE(SUM('Sample Data Clean Test Names'[DispensedQuantity]))
)
, "previousDate"
,var _date = [Date]
return
CALCULATE(
LASTNONBLANK('Calendar'[Date] , [_NullCheck])
, FILTER(
ALL('Calendar'[Date])
, 'Calendar'[Date] < _date
)
) + 1
)
return
MAXX(
t
, DATEDIFF([previousDate] , [Date] , DAY) + 1
)``````

This allows to create a report like so:

The second measure can be created accordingly by using less grouping columns, I have no idea how the event will influence the output as the usage is not described.

Hopefully this is what you are looking for, at least it provides some ideas how to solve your requirements.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
7 REPLIES 7
Super User

## Re: DAX formula for consecutive events with some criteria

Hey,

please provide an Excel file that contains the sample data that you are using to demonstrate what you are looking for. Upload the Excel file to ondrive or dropbox and share the link.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Super Contributor

## Re: DAX formula for consecutive events with some criteria

Hey @TomMartens ,

Now I'm curious. 😁

PBIX

Super User

## Re: DAX formula for consecutive events with some criteria

Thanks @mwegener

Appreciate the effort a lot. Please be aware that I have to take a walk with my girlfriend. As soon as we are back I will provide the solution.

Regards,

Tom

P.S.: Hope we will meet soon.

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Frequent Visitor

## Re: DAX formula for consecutive events with some criteria

Attached is the link to the file on dropbox. If you have any questions let me know, thanks so much!

https://www.dropbox.com/sh/rf109s3z6atvinm/AAB4-ws2H5jsanrxGqfXvDDPa?dl=0

Frequent Visitor

## Re: DAX formula for consecutive events with some criteria

Sorry I should also explain that the screenshot was a simplified version of the actual data. Here is how the data maps to the screenshot:

Date = FillDate

Person = patientname

Product = drugname

quantity = drug strength

event = recordtype (F is Fill, which is what I want)

Super User

## Re: DAX formula for consecutive events with some criteria

Hey @tredeuce ,

i guess you owe @mwegener  a drink, as he prepared a pbix based on your inital screenshot, that did not match the data that you finally provided.

Nevertheless, thank you for providing sample data.

Here is my solution.

First I created a calendar table using this DAX statement:

``````Calendar =
var YearMin = YEAR(MIN('Sample Data Clean Test Names'[FillDate]))
var YearMax = YEAR(MAX('Sample Data Clean Test Names'[FillDate]))
var DateStart = DATE(YearMin , 1 , 1)
var DateEnd = DATE(YearMax , 12 , 31)
return
CALENDAR(DateStart , DateEnd)
, "RunningDayIndex" , DATEDIFF(DateStart , [Date] , DAY) + 1
)``````

Please be aware that the column RunningDayIndex is not used, but as more complex "sequence math" works better using integers creating integer columns inside my calendar table has become a habit.

Next I created a simple measure, I use this measure to discover/flag a missing value, as this resets the sequence. For this I used the column "DispensedQuantity", the measure looks like this:

``````_NullCheck =
var _test = CALCULATE(SUM('Sample Data Clean Test Names'[DispensedQuantity]))
return
IF(ISBLANK(_test) , 1 , BLANK())``````

The measure that counts the number of consecutive days per patientname, drugname, drugstrength looks like this:

``````consecutive days v1 =
var t =
SUMMARIZE(
'Sample Data Clean Test Names'
, 'Sample Data Clean Test Names'[PatientName]
, 'Sample Data Clean Test Names'[DrugName]
, 'Sample Data Clean Test Names'[DrugStrength]
, 'Calendar'[Date]
)
, "dispensedQuantity", CALCULATE(SUM('Sample Data Clean Test Names'[DispensedQuantity]))
)
, "previousDate"
,var _date = [Date]
return
CALCULATE(
LASTNONBLANK('Calendar'[Date] , [_NullCheck])
, FILTER(
ALL('Calendar'[Date])
, 'Calendar'[Date] < _date
)
) + 1
)
return
MAXX(
t
, DATEDIFF([previousDate] , [Date] , DAY) + 1
)``````

This allows to create a report like so:

The second measure can be created accordingly by using less grouping columns, I have no idea how the event will influence the output as the usage is not described.

Hopefully this is what you are looking for, at least it provides some ideas how to solve your requirements.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Super User

## Re: DAX formula for consecutive events with some criteria

Or if you wanted to created these as calculated columns so that you could then do slicing or filtering on the number of consecutive days you could use calculated columns like the following (based off your original screenshot data):

``````Consecutive Prod =
VAR CurrentDate = 'Table'[Date]
VAR CurrentProduct = 'Table'[Product]
VAR CurrentPerson = 'Table'[Person]
VAR CurrentEvent = 'Table'[Event]
RETURN COUNTROWS (
FILTER (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Product] )
),
'Table'[Date] <= CurrentDate
&& 'Table'[Event] = CurrentEvent
&& 'Table'[Product] = CurrentProduct
&& 'Table'[Person] = CurrentPerson
)
)``````

and

``````Consecutive Prod Qty =
VAR CurrentDate = 'Table'[Date]
VAR CurrentProduct = 'Table'[Product]
VAR CurrentPerson = 'Table'[Person]
VAR CurrentEvent = 'Table'[Event]
VAR CurrentQty = 'Table'[Qty]
RETURN COUNTROWS (
FILTER (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Product] )
),
'Table'[Date] <= CurrentDate
&& 'Table'[Event] = CurrentEvent
&& 'Table'[Product] = CurrentProduct
&& 'Table'[Person] = CurrentPerson
&& 'Table'[Qty] = CurrentQty
)
)
``````

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)