DAX formula for consecutive events with some criteria

[Anonymous]

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.

[TomMartens]

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

[Anonymous]

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

[Anonymous]

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)

[TomMartens · Accepted solution]

Hey @Anonymous ,

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:

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

[d_gosbell]

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

[mwegener]

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.

[TomMartens]

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.