Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Return Aging number

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 idmonitoring monitoring date Agining
1absent1/1/20191
1 no absent10/1/20191
1 no absent12/1/20191
1absent15/1/20192
1absent18/1/20195
2 no absent1/1/20190
2 no absent8/1/20190
2absent20/1/20191
2absent25/1/20195
2 no absent26/1/20195

 

1 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   www.daxformatter.com

 

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Not applicable

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.

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   www.daxformatter.com

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.