Need help with DAX cumulative conditional aggregation

[mvrali]

I am working on creating injury logs. Here is the sample data:

Case	Type	Start Date	Days
X	LD	9/1/2017	100
X	LD	12/1/2017	20
X	LT	10/1/2017	40
X	LT	11/1/2017	80
Y	LT	10/1/2017	50
Y	LT	9/1/2017	50
Y	LD	10/1/2017	70
Y	LD	11/1/2017	90
Y	LT	12/1/2017	30

I need to aggregate the total periods for Cases X and Y based on the Type (LD or LT). The rules are:

• Total of LD and LT capped at 180
• Do the calcualtion until reaching 180 total  based on the date (include the ones that have earlier dates and their sum is less than 180)
• if reaching the cap in the middle of a period (Yellow colors) then just use part of it until reaching 180.
• Green means the ones that should be used, Yellow partially used to reach the cap, and red means it was not used.

Here is the results I am looking for:

	LD	LT
X	120	60
Y	50	130

I appreciate your help!

[v-shex-msft · Accepted solution]

Hi @mvrali,

It seems like you need to calculate based on specific sort order, I think you need to add index column to calculate loop table.

Steps:

1. Enter into query editor and add index column.

2. Add calculated column to change days based on rolling target.

Spoiler

Filtered Days =
VAR _previous =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                && [Index] < EARLIER ( Test[Index] )
        ),
        [Days]
    )
VAR _current =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                && [Index] <= EARLIER ( Test[Index] )
        ),
        [Days]
    )
RETURN
    IF (
        _current <= 180,
        [Days],
        IF ( _previous < 180 && _current > 180, 180 - _previous, 0 )
    )

3. Create matrix visual with above columns.

Regards,

Xiaoxin Sheng

[mvrali]

@v-shex-msft,

Worked great! Thanks for the soloution.