DAX help needed - Sum ranges

[dkay84_PowerBI]

I am connected to a tabular cube, so measures only please!

I have time series data (a revenue value) at monthly grain, and I have specific dates that are flagged for an event occurring.  Some of these events are one time, while others are ongoing for a period (I have the business knowledge to differentiate the two).  Any of the events (either one time or ongoing) can have multiple periods where they occur over the time series.

So, imagine that my table looks like this:

Date        | Event 1 Flag | Event 2 Flag | Event 3 Flag

1/1/2015 | 0                     | 0                    | 0

2/1/2015 | 1                     | 0                    | 0

3/1/2015 | 0                     | 1                    | 0

4/1/2015 | 0                     | 1                    | 0

5/1/2015 | 0                     | 0                    | 1

My goal is to have a measure that calculates the duration of the ongoing events (in this example, Event #2 is ongoing).  I cannot just sum the column though becuase there could be another period in the future where Event 2 is flagged.  So, I need to sum only the continuous range of Event 2 Flag = 1

[v-shex-msft · Accepted solution]

Hi @dkay84_PowerBI,

You can refer to below step to get sum of continuous date range total.

Steps:

1. Create a table with tag column mark the continued date range.

NewTable = 
VAR current_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR previous = current_date - 1
VAR next = current_date + 1
VAR filtedTable =
    FILTER ( ALL ( 'Table' ), [Event 2] = 1 )
VAR dateRange =
    SELECTCOLUMNS ( filtedTable, "Date", [Date] )
    var Tagged=ADDCOLUMNS (
        filtedTable,
        "Tag", IF (
            OR (
                AND ( [Date] IN dateRange, [Date] + 1 IN dateRange ),
                AND ( [Date] IN dateRange, [Date] - 1 IN dateRange )
            ),
            1,
            0
        )
    )
RETURN
UNION(ADDCOLUMNS(FILTER('Table',[Event 2]=0),"Tag",0),Tagged)

2. Write a formula to summary continued date range.

Running Total =
VAR range_min =
    MAXX (
        FILTER ( ALL ( NewTable ), [Date] < MAX ( [Date] ) && [Tag] = 0 ),
        [Date]
    )
VAR range_max =
    MINX (
        FILTER ( ALL ( NewTable ), [Date] > MAX ( [Date] ) && [Tag] = 0 ),
        [Date]
    )
RETURN
    IF (
        MAX ( [Tag] ) <> 0,
        IF (
            range_min <> BLANK (),
            DATEDIFF ( range_min, range_max, DAY ) - 1,
            DATEDIFF ( FIRSTDATE ( ALLSELECTED ( NewTable[Date] ) ), range_max, DAY )
        )
    )

Result:

Regards,

Xiaoxin Sheng