Cumulated count of distinct dates in date ranges using DAX

Question

I'm still new to PowerPivot and tabular models and it's hard work switching from relational DBs.

I have two tables in power pivot: Logbook and Calendar - they are not linked. Logbook rows have date ranges specified using [Start Date] and [End Date] columns. I have Logbook rows grouped by [Vehicle ID] and I've added a sequential [Row ID] for each of the [Vehicle ID] groups.

The Logbook date ranges overlap each other and I'm trying to write a measure that gives the cumulative count of distinct days in the date ranges.

Logbook Table:

Vehicle ID  Row ID  Start Date   End Date      *Cumulated Logged Days (Expected Result)*
AAA         1       1 Jun 2016   1 June 2016   1
AAA         2       1 Jun 2016   5 June 2016   5
AAA         3       10 Jun 2016  10 June 2016  6
BBB         1       1 Jun 2016   1 June 2016   1
BBB         2       5 Jun 2016   5 June 2016   2
BBB         3       7 Jun 2016   7 June 2016   3

This is my best attempt…

Cumulated Logged Days:
=CALCULATE(
    COUNTROWS('Calendar'),
    FILTER(
        ALL(Logbook),
        COUNTROWS(
            FILTER(
                Logbook,
                EARLIER(Logbook[Vehicle ID]) = [Vehicle ID]
                && EARLIER(Logbook[Row ID]) <= [Row ID]
            )
        )
    ),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= MIN(Logbook[Start Date])
        && 'Calendar'[Date] <= MAX(Logbook[End Date])
    )
)

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-10T18:25:42

They say divide and conquer and that's what I'd do in your case. Basically, The first calculation needed is the number of days between start and end dates. Once you have the difference between both dates you can cumulatively sum these values. Let's implement it in DAX.

Create a calculated column in the LogBook table called DaysCount with the following expression:

DaysCount :=
IF ( [Start Date] < [End Date], DATEDIFF ( [Start Date], [End Date], DAY ), 1 )

Now create a measure to get cumulative count of days:

Cumulated Logged Days :=
VAR currentVehicle =
    LASTNONBLANK ( LogBook[Vehicle ID], 0 )
RETURN
    CALCULATE (
        SUM ( LogBook[DaysCount] ),
        FILTER (
            ALL ( LogBook ),
            LogBook[Row ID] <= MAX ( LogBook[Row ID] )
                && currentVehicle = LogBook[Vehicle ID]
        )
    )

UPDATE DATEDIFF function is supported in Excel 2016 so if you have a different version the DaysCount calculated column wont work

Alternative for DaysCount calculated column (Tested in Excel 2013):

=IF([Start Date]<[End Date], 1 * ([End Date]-[Start Date]),1)

Also support for Variables was included in DAX 2015 (Excel 2016) so if you have a earlier version you have to use this expression for the Cumulated Logged Days measure.

Cumulated Logged Days :=
CALCULATE (
    SUM ( LogBook[DaysCount] ),
    FILTER (
        ALL ( LogBook ),
        COUNTROWS (
            FILTER ( LogBook, EARLIER ( LogBook[Vehicle ID] ) = LogBook[Vehicle ID] )
        )
            && LogBook[Row ID] <= MAX ( LogBook[Row ID] )
    )
)

Final result in a Excel 2013 PivotTable.

Let me know if this helps.

Cumulated count of distinct dates in date ranges using DAX

1 Answers