Forum Discussion
Solved
Need help with DAX cumulative conditional aggregation
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...
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.
SpoilerFiltered 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
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
Anonymous,
Worked great! Thanks for the soloution.
