Return Aging number

[Anonymous]

Hi Every One,

I want Help.

I have student ID 1,2.

I want to retrieve  the  number of days  that student absent based on the first monitoring date if  the monitoring equal absent must be  incremental date aging otherwise again not  incremental date, you can  find below as sample table with correct resultes

st id	monitoring	monitoring date	Agining
1	absent	1/1/2019	1
1	no absent	10/1/2019	1
1	no absent	12/1/2019	1
1	absent	15/1/2019	2
1	absent	18/1/2019	5
2	no absent	1/1/2019	0
2	no absent	8/1/2019	0
2	absent	20/1/2019	1
2	absent	25/1/2019	5
2	no absent	26/1/2019	5

[parry2k]

@Anonymous not able to understand your calculation in sample data, how you are getting aging in both the cases for student 1 and 2

[Anonymous]

thanks @parry2k ,

knindly note that if monitoring = absent must be calculate Aging so, 

for  student 1 In date 1/1/2019 and monitoring = absent, Aging  =1

for  student 1 In date 10/1/2019 and monitoring =  not absent, Aging  still  =1

for  student 1 In date 12/1/2019 and monitoring =  not absent, Aging  still  =1

for  student 1 In date 15/1/2019 and monitoring =  absent, Aging  1 + 1=2 mean student have two days absent todate 15/1/2019,

for  student 1 In date 18/1/2019 and monitoring =  absent, Aging  2 + day((18/1/2019) - (15/1/2019)) 3= 5 mean student have five absent todate 18/1/2019....etc and the same scenario for student 2........

hoped to get your help.

and thanks advance,

[Anonymous]

hi mohammed, I think you can add a rank column to sort student 1 and 2 separately by monitoring date. For a single student, if two rank numbers are k and k+1, and both monitoring = absent, then monitoring date1 - monitoring date2 + aging(monitoring date2). You may try it on your side.

[AlB · Accepted solution]

Hi @Anonymous,

There seems to be an inconsistency in the expected result. Following the logic you describe, the last two rows in the 'Agining' column should be 6 instead of 5, right?

Try creating these two calculated columns. It can probably be done in a simpler way but this seems to work. See it all at work in the attached file.

IndividualSum =
VAR _BlockId =
    IF (
        Table1[monitoring] = "absent";
        VAR _PreviousDate =
            CALCULATE (
                MAX ( Table1[monitoring date] );
                Table1[monitoring date] < EARLIER ( Table1[monitoring date] );
                ALLEXCEPT ( Table1; Table1[st id] )
            )
        VAR _PreviousMonitoring =
            CALCULATE (
                DISTINCT ( Table1[monitoring] );
                Table1[monitoring date] = _PreviousDate;
                ALLEXCEPT ( Table1; Table1[st id] )
            )
        RETURN
            IF (
                _PreviousMonitoring IN { BLANK (); "no absent" };
                Table1[monitoring date];
                VAR _PreviousNonAbsentDate =
                    CALCULATE (
                        MAX ( Table1[monitoring date] );
                        Table1[monitoring date] < EARLIER ( Table1[monitoring date] );
                        Table1[monitoring] = "no absent";
                        ALLEXCEPT ( Table1; Table1[st id] )
                    )
                VAR _FirstAbsentDateThisBlock =
                    CALCULATE (
                        MIN ( Table1[monitoring date] );
                        Table1[monitoring date] > _PreviousNonAbsentDate;
                        Table1[monitoring] = "absent";
                        ALLEXCEPT ( Table1; Table1[st id] )
                    )
                RETURN
                    _FirstAbsentDateThisBlock
            )
    )
VAR _IndividualSum =
    IF (
        NOT ISBLANK ( _BlockId );
        IF (
            _BlockId = Table1[monitoring date];
            1;
            DATEDIFF ( _BlockId; Table1[monitoring date]; DAY )
        )
    )
RETURN
    _IndividualSum

ExpectedCol =
CALCULATE (
    SUM ( Table1[IndividualSum] );
    Table1[monitoring] = "absent";
    Table1[monitoring date] <= EARLIER ( Table1[monitoring date] );
    ALLEXCEPT ( Table1; Table1[st id] )
) + 0

Code formatted with