cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tredeuce Frequent Visitor
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.

 

Consecutive Events.JPG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
ADDCOLUMNS(
    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 =
ADDCOLUMNS( 
    ADDCOLUMNS(
        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:

image.png

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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Super User
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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
mwegener Super Contributor
Super Contributor

Re: DAX formula for consecutive events with some criteria

Hey @TomMartens ,

 

Now I'm curious. 😁

 

PBIX

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Super User
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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
tredeuce Frequent Visitor
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

tredeuce Frequent Visitor
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
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
ADDCOLUMNS(
    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 =
ADDCOLUMNS( 
    ADDCOLUMNS(
        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:

image.png

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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Super User
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] = "buy"
        && '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] = "buy"
        && 'Table'[Event] = CurrentEvent
        && 'Table'[Product] = CurrentProduct 
        && 'Table'[Person] = CurrentPerson
        && 'Table'[Qty] = CurrentQty
    )
)

  

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)