Need help with DAX cumulative conditional aggregation

Question

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!

anonymous · Accepted Answer

Hi&nbsp;mvrali,
&nbsp;
It seems like you need to calculate based on specific sort order, I think you need to add index column to calculate loop table.
&nbsp;
Steps:
1. Enter into query editor and add index column.
2. Add calculated column to change days based on rolling target.
Spoiler (Highlight to read)
Filtered Days =
VAR _previous =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                &amp;&amp; [Index] &lt; EARLIER ( Test[Index] )
        ),
        [Days]
    )
VAR _current =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                &amp;&amp; [Index] &lt;= EARLIER ( Test[Index] )
        ),
        [Days]
    )
RETURN
    IF (
        _current &lt;= 180,
        [Days],
        IF ( _previous &lt; 180 &amp;&amp; _current &gt; 180, 180 - _previous, 0 )
    )

Filtered Days =
VAR _previous =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                &amp;&amp; [Index] &lt; EARLIER ( Test[Index] )
        ),
        [Days]
    )
VAR _current =
    SUMX (
        FILTER (
            Test,
            [Case] = EARLIER ( Test[Case] )
                &amp;&amp; [Index] &lt;= EARLIER ( Test[Index] )
        ),
        [Days]
    )
RETURN
    IF (
        _current &lt;= 180,
        [Days],
        IF ( _previous &lt; 180 &amp;&amp; _current &gt; 180, 180 - _previous, 0 )
    )

3. Create matrix visual with above columns.

&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Need help with DAX cumulative conditional aggregation

2 Replies

Related content

Cumulative Total

Creating Cumulative Graphs

Data Aggregation issue

Year to date cumulative

Cumulative Total