[DAX] Counting consecutive days of an event

[DDalmas]

Hello everyone, 

I have to count the maximum of consecutive days that a vehicle has worked. 
The fact table contains the vehicle_ID, the date and the journey_id. 
Journey_id is the register of the every single time the driver turns on the vehicle. It can happen many times during the day. 

Is it possible? 

Thanks in advance 🙂 

[AlB · Accepted solution]

@DDalmas 

Following a similar approach to earlier

1. Place Vehicle_Id in a table visual

2. Create this measure

Max number Inactive Days per vehicle = 
    VAR summT0_ = DISTINCT( Table1[Date] )
    VAR allDatesInPeriod_ = SELECTCOLUMNS(GENERATESERIES(MIN(Table1[Date]), MAX(Table1[Date])), "Date",[Value])
    VAR summT_ = EXCEPT(allDatesInPeriod_,summT0_)
    VAR auxT_ =
        FILTER (
            ADDCOLUMNS (
                summT_,
                "index_",
                    COUNTROWS ( FILTER ( summT_, [Date] <= EARLIER ( [Date] ) ) ) 
            ),
            VAR nextDate_ =
                MINX ( FILTER ( summT_, [Date] > EARLIER ( [Date] ) ), [Date] )
            RETURN
                 ( nextDate_ - [Date] ) <> 1
        )
    VAR res_ =
        MAXX (
            auxT_,
            [index_]
                - MAXX ( FILTER ( auxT_, [index_] < EARLIER ( [index_] ) ), [index_] )
        )
    RETURN
         res_

3. Create this other measure that uses the previous one:

Max number Inactive Days per vehicle TOT = 
MAXX ( DISTINCT ( Table1[Vehicle_ID] ), [Max number Inactive Days per vehicle] )

4. Place [Max number Inactive Days per vehicle TOT] in the visual to see the maximum number of consecutive INACTIVE days per vehicle and the total (i.e the max of the max per vehicle). You can also place this measure in a card visual to see only the max of the max  (for all vehicles)

5. See it all at work in the attached file with a very simplified fact table 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[DDalmas]

@AlB Thank you very much for this idea. I will be trying to do it tomorrow.

I am new here, sorry for the print screen. Next time, I will attach the pbix. 

[AlB]

Hi @DDalmas 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix.

1. Place Vehicle_Id in a table visual

2. Create this measure

Max per vehicle = 
VAR summT_ =
    SUMMARIZE ( Table1, Table1[Vehicle_ID], Table1[Date] )
VAR auxT_ =
    FILTER (
        ADDCOLUMNS (
            summT_,
            "index_",
                COUNTROWS ( FILTER ( summT_, [Date] <= EARLIER ( [Date] ) ) ) 
        ),
        VAR nextDate_ =
            MINX ( FILTER ( summT_, [Date] > EARLIER ( [Date] ) ), [Date] )
        RETURN
             ( nextDate_ - [Date] ) <> 1
    )
VAR res_ =
    MAXX (
        auxT_,
        [index_] - MAXX ( FILTER ( auxT_, [index_] < EARLIER ( [index_] ) ), [index_] )
    )
RETURN
    res_

3. Create this other measure that uses the previous one:

Max of Max per vehicle = 
MAXX ( DISTINCT ( Table1[Vehicle_ID] ), [Max per vehicle] )

 4. Place [Max of Max per vehicle] in the visual to see the  maximum number of consecutive days per vehicle and the total (i.e the max of the max per vehicle). You can also place this measure in a card visual to see only the max of the max  (for all vehicles)

 5. See it all at work in the attached file with a very simplified fact table 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[DDalmas]

Dear @AlB 

I've tryed to do it, but it does not seems to work properly. 
Maybe, I haven't explained very well. I have to display in a table with all vehicles, a colum containing the maximum number of day that every single vehicle has not worked. 
Maybe the measure has to count the consecutive blank days, because I did the croossjoin between all vehicles_ids and all possible dates. I don't know if it will work. 
I will attach a sample of the original table from the datawarehouse. It contains the last 10 days. 

table1 

Thanks for your help, again 🙂 

[AlB]

@DDalmas 

I cannot access the link. Seems to require a sign in with a Google account. Either remove that requirement or post it elsewhere (Dropbox, tinyupload.com, etc)

You explained exactly the opposite on your initial posting:

I have to count the maximum of consecutive days that a vehicle has worked.

I'd need a more detailed, accurate explanation before maknig another attempt. You talk about inactive days. What will be considered inactive days in your data? Is this on a specific period of time? For instance, if a vehicle has data on these dates (MM/DD/Year):

01/01/2019

01/02/2019

01/04/2019

01/06/2019  

How many inactive days is that? Do we count any inactive day before 01/01/2019 or after 01/06/2019? How?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[DDalmas]

Hi, 

Please find the file here - TABLE1 (fact journey) TABLE2  (dim vehicles)

Inactive day will be considered any day that there is no journey ID for the vehicle. 

The dashboard has a date filter, so the client can check the how many days the vehicle has not worked in the last semester or in the last week. We can start counting from the first date registered on the table. It will be date one. 

I am not sure if it is possible yet. 

Example: 

I tryed to create a crossjoin table to summarize all vehicles and dates possibilities: 

TESTE = 
    CROSSJOIN(SELECTCOLUMNS('corteva dim_vehicles',"Vehicle_id",'corteva dim_vehicles'[vehicle_id]),DISTINCT('corteva fact_journeys'[Data]))

And counted the number of journeys that the vehicles has done in that day:

Journeys = CALCULATE(DISTINCTCOUNT('corteva fact_journeys'[journey_id]), FILTER('corteva fact_journeys','corteva fact_journeys'[vehicle_id]=TESTE[Vehicle_id]),FILTER('corteva fact_journeys','corteva fact_journeys'[Data]=TESTE[Data]))

The wanted output is something like that: 

 

I hope I was able to explain it better.