calculate active cases and slice it based on other columns

[Chetanab]

I have the below table. I need to  calculate active cases by end of month and have it work when I use a slicer with other columns ( for eg: term column)

ID	CreateDate	Status change date	changeDateEOM	Status	Term
1	3-Jan-22	1/12/2022	31-Jan-22	Active	Short Term
2	5-Jan-22	1/13/2022	31-Jan-22	Active	Short Term
2	5-Jan-22	2/2/2022	28-Feb-22	Resolved	Medium Term
3	5-Jan-22	1/5/2022	31-Jan-22	Active	Short Term
3	5-Jan-22	2/20/2022	28-Feb-22	Resolved	Short Term
4	5-Jan-22	1/5/2022	31-Jan-22	Active	Short Term
4	5-Jan-22	2/3/2022	28-Feb-22	Resolved	Short Term
4	5-Jan-22	3/3/2022	31-Mar-22	Active	Long Term
5	5-Jan-22	1/5/2022	31-Jan-22	Active	Medium Term
5	5-Jan-22	2/3/2022	28-Feb-22	Resolved	Medium Term
5	5-Jan-22	3/3/2022	31-Mar-22	Resolved	Medium Term
6	5-Jan-22	2/3/2022	28-Feb-22	Active	Short Term
6	5-Jan-22	3/3/2022	31-Mar-22	Resolved	Short Term

I need to obtain the term value of these Active IDs as of end of month date - 

expected result

End of month	Short Term	Medium Term	Long Term
31-Jan-22	5	1	0
28-Feb-22	2	0	0
31-Mar-22	1	0	1

as of 31-jan (all IDs were active)=> IDs 1,2,3,4,6 were short term and ID 5 was medium term
As of 28 feb=> Active IDs 1&6 are short term
as of 31 mar=> active IDs 1 was short term, 4 was long term

Please help. 

[philouduv]

Hey @Chetanab ,

An other has this problem : 

Solved: counting rows based on multiple dates - Microsoft Power BI Community

The context is a bit different but tell me if you can adapt it

If not I will be more than happy to help.

Best regards

ps: Do not hesitate to create a table for with each month and add the column long short and medium term for the expect output

[Chetanab]

yes, the context is different. Also the terms are not defined. An ID can have any of the 3 term values. Can you please look into the below DAX I have and suggest where I am going wrong?

# Active =

VAR __eom = MAX( EoM[Date] )

Var _st =

COUNTROWS(

FILTER(

CALCULATETABLE( VALUES( Sheet1[ID] ), Sheet1[CreateDate] <= __eom ),

CALCULATE(

LASTNONBLANKVALUE( Sheet1[Status change date], MAX( Sheet1[Status] ) ),

Sheet1[Status change date] <= __eom,ALLEXCEPT(sheet1,Sheet1[Term])

) <> "Resolved"

)

)

return _st

[v-rzhou-msft · Accepted solution]

Hi @Chetanab ,

Here I have two ways to achieve your goal.

Way 1. Create a calculated table and then get result based on this new table.

Expand Table = 
VAR _BASIC =
    GENERATE (
        SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[CreateDate] ),
        VALUES ( 'Table'[changeDateEOM] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _BASIC,
        "Status change date",
            CALCULATE (
                MAX ( 'Table'[Status change date] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && YEAR ( 'Table'[Status change date] ) * 100
                            + MONTH ( 'Table'[Status change date] )
                            = YEAR ( EARLIER ( [changeDateEOM] ) ) * 100
                                + MONTH ( EARLIER ( [changeDateEOM] ) )
                )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Fill",
            VAR _MIN =
                MINX (
                    FILTER (
                        _ADD1,
                        [ID] = EARLIER ( [ID] )
                            && [changeDateEOM] > EARLIER ( [changeDateEOM] )
                    ),
                    [Status change date]
                )
            VAR _MAX =
                MAXX (
                    FILTER (
                        _ADD1,
                        [ID] = EARLIER ( [ID] )
                            && [changeDateEOM] < EARLIER ( [changeDateEOM] )
                    ),
                    [Status change date]
                )
            VAR _RESULT1 =
                IF ( ISBLANK ( [Status change date] ), _MIN, [Status change date] )
            RETURN
                IF ( _RESULT1 = BLANK (), _MAX, _RESULT1 )
    )
VAR _ADD3 =
    ADDCOLUMNS (
        _ADD2,
        "Status",
            CALCULATE (
                MAX ( 'Table'[Status] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && 'Table'[Status change date] = EARLIER ( [Fill] )
                )
            ),
        "Term",
            CALCULATE (
                MAX ( 'Table'[Term] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && 'Table'[Status change date] = EARLIER ( [Fill] )
                )
            )
    )
RETURN
    _ADD3

Measure:

Measure = 
CALCULATE(COUNT('Expand Table'[ID]),'Expand Table'[Status]<>"Resolved")

Way2. Create this table as vritual table in measure and then use dim Eom and Term table to get result.

Create two unrelated Dimtables.

Term = VALUES('Table'[Term])

EOM = VALUES('Table'[changeDateEOM])

Measure:

Measure 2 = 
VAR _BASIC =
    GENERATE (
        SUMMARIZE (ALL( 'Table'), 'Table'[ID], 'Table'[CreateDate] ),
        CALCULATETABLE( VALUES ( 'Table'[changeDateEOM] ),ALL('Table'))
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _BASIC,
        "Status change date",
            CALCULATE (
                MAX ( 'Table'[Status change date] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && YEAR ( 'Table'[Status change date] ) * 100
                            + MONTH ( 'Table'[Status change date] )
                            = YEAR ( EARLIER ( [changeDateEOM] ) ) * 100
                                + MONTH ( EARLIER ( [changeDateEOM] ) )
                )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Fill",
            VAR _MIN =
                MINX (
                    FILTER (
                        _ADD1,
                        [ID] = EARLIER ( [ID] )
                            && [changeDateEOM] > EARLIER ( [changeDateEOM] )
                    ),
                    [Status change date]
                )
            VAR _MAX =
                MAXX (
                    FILTER (
                        _ADD1,
                        [ID] = EARLIER ( [ID] )
                            && [changeDateEOM] < EARLIER ( [changeDateEOM] )
                    ),
                    [Status change date]
                )
            VAR _RESULT1 =
                IF ( ISBLANK ( [Status change date] ), _MIN, [Status change date] )
            RETURN
                IF ( _RESULT1 = BLANK (), _MAX, _RESULT1 )
    )
VAR _ADD3 =
    ADDCOLUMNS (
        _ADD2,
        "Status",
            CALCULATE (
                MAX ( 'Table'[Status] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && 'Table'[Status change date] = EARLIER ( [Fill] )
                )
            ),
        "Term",
            CALCULATE (
                MAX ( 'Table'[Term] ),
                FILTER (
                    'Table',
                    'Table'[ID] = EARLIER ( [ID] )
                        && 'Table'[Status change date] = EARLIER ( [Fill] )
                )
            )
    )
RETURN
COUNTAX(FILTER(_ADD3,[changeDateEOM] = MAX(EOM[changeDateEOM]) && [Term] = MAX(Term[Term]) && [Status] <>"Resolved"),[ID])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chetanab]

Thank you @v-rzhou-msft . 

[Chetanab]

I have this as of now, it doesnt give me the expected result

# Active =

VAR __eom = MAX( EoM[Date] )

Var _st =

COUNTROWS(

FILTER(

CALCULATETABLE( VALUES( Sheet1[ID] ), Sheet1[CreateDate] <= __eom ),

CALCULATE(

LASTNONBLANKVALUE( Sheet1[Status change date], MAX( Sheet1[Status] ) ),

Sheet1[Status change date] <= __eom,ALLEXCEPT(sheet1,Sheet1[Term])

) <> "Resolved"

)

)

return _st