Formulae for repeat usage across rows

[vibhoryadav23]

Hi,

I need to find users who have been present in the data in last 3 month and twice in last three month (incl. current month) like shown in the examples below respectively. Column 1 and 2 are inputs and 2 and 3 are the required outcomes. In the example, the 'User1' is present in all the 6 month and hence we know from 3rd month onwards, it has been present in all the month and hence yes for all.

Users	Months	Present in last 3 months (incl. current month)	Present in 2 of last 3 months (incl. current month)
User1	1	0	0
User1	2	0	1
User1	3	1	1
User1	4	1	1
User1	5	1	1
User1	6	1	1
User2	3	0	0
User2	4	0	1
User2	5	1	1
User2	6	1	1
User3	5	0	0
User4	4	0	0
User4	5	0	1
User5	3	0	0
User5	5	0	1

++ adding an example of a user. Clubbed year and month to form 'Period' to take into account historic data.

Users	Period	Present all in last 3 months	Present 2 in last 3 months
X	202403	0	1
X	202401	0	1
X	202311	0	1
X	202310	0	0
X	202303	0	1
X	202302	0	0
X	202211	1	1
X	202210	1	1
X	202209	1	1
X	202208	1	1
X	202207	1	1
X	202206	1	1
X	202205	1	1
X	202204	1	1
X	202203	1	1
X	202202	1	1
X	202201	1	1
X	202112	0	1
X	202111	0	0

[Greg_Deckler]

@vibhoryadav23  Try these. PBIX file is attached:

Column = 
    VAR __User = [Users]
    VAR __Month = [Months]
    VAR __Count = COUNTROWS( FILTER( 'Table', [Users] = __User && [Months] <= __Month ) )
    VAR __Result = IF( __Count >= 3, 1, 0 )
RETURN
    __Result


Column 2 = 
    VAR __User = [Users]
    VAR __Month = [Months]
    VAR __Count = COUNTROWS( FILTER( 'Table', [Users] = __User && [Months] <= __Month ) )
    VAR __Result = IF( __Count >= 2, 1, 0 )
RETURN
    __Result

Alternate forms that are perhaps a bit more selective are these:

Column = 
    VAR __User = [Users]
    VAR __Month = [Months]
    VAR __Count = COUNTROWS( FILTER( 'Table', [Users] = __User && [Months] <= __Month && [Months] >= __Month - 2 ) )
    VAR __Result = IF( __Count >= 3, 1, 0 )
RETURN
    __Result


Column 2 = 
    VAR __User = [Users]
    VAR __Month = [Months]
    VAR __Count = COUNTROWS( FILTER( 'Table', [Users] = __User && [Months] <= __Month && [Months] >= __Month - 2 ) )
    VAR __Result = IF( __Count >= 2, 1, 0 )
RETURN
    __Result

[vibhoryadav23]

@Greg_Deckler I took column 1 form your first set and column 2 from second. Your solution mostly works well except when the months are missing in between, it doesnt take them into account. That's the main problem I am trying to solve. Example: I have combined year and month to form 'Period' as the data can back years in time. If you observe first three rows, there are some months missing for the user but its still being counted in Column 1. For column 2, in second row it should be 1 as its present in 202401 and 202311 (two of last 3 months)

Thanks for the effort btw. Much appriciated!

I have added an example of a user in the original post for more clarity.

[Greg_Deckler]

@vibhoryadav23 That was the purpose of the second set of example column formulas. Those *should* work in the case you are describing. You would want to use column1 and column2 from the second set as these filter the number of rows that are looked back upon so if there are missing months then you *should* get the correct results.

[vibhoryadav23]

These are the result from 2nd set. Mostly works with some exceotions. I believe it doesnt work when there is change in year. So from 202401 and 202312, it wont be able to take this as a previous month with simple subtraction.Can we fix these as well?

[Greg_Deckler · Accepted solution]

@vibhoryadav23 OK, this should do it. PBIX is attached below signature.

Column = 
    VAR __User = [Users]
    VAR __Period = [Period]
    VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1)
    VAR __EndDate = EOMONTH(__PeriodDate, -2)
    VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate)
    VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) )
    VAR __Result = IF( __Count >= 3, 1, 0 )
RETURN
    __Result


Column 2 = 
    VAR __User = [Users]
    VAR __Period = [Period]
    VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1)
    VAR __EndDate = EOMONTH(__PeriodDate, -2)
    VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate)
    VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) )
    VAR __Result = IF( __Count >= 2, 1, 0 )
RETURN
    __Result

[vibhoryadav23]

Amazing. This works.

Btw I was able to solve it by ranking the period column and using it in your formula instead of period, but your new solution is even better.

Thanks @Greg_Deckler 

[Greg_Deckler]

@vibhoryadav23 Oh, it's the year changes. I had actually wondered about that originally but the original data didn't include it. Hold please.

[govindarajan_d]

Hi @vibhoryadav23 ,

Can you try the below measures:

Present in last 3 months (incl. current month):

Op1_Measure =
IF (
    COUNTX (
        WINDOW (
            -2,
            REL,
            0,
            REL,
            SUMMARIZE ( ALL ( 'Table' ), 'Table'[Users], 'Table'[Months] ),
            ORDERBY ( 'Table'[Months] ),
            DEFAULT,
            PARTITIONBY ( 'Table'[Users] )
        ),
        CALCULATE ( COUNT ( 'Table'[Months] ) )
    ) = 3,
    1,
    0
)

Present in 2 of last 3 months (incl. current month):

Op2_Measure =
IF (
    COUNTX (
        WINDOW (
            -2,
            REL,
            0,
            REL,
            SUMMARIZE ( ALL ( 'Table' ), 'Table'[Users], 'Table'[Months] ),
            ORDERBY ( 'Table'[Months] ),
            DEFAULT,
            PARTITIONBY ( 'Table'[Users] )
        ),
        CALCULATE ( COUNT ( 'Table'[Months] ) )
    ) >= 2,
    1,
    0
)

Tested:

Op1_Measure and Op2_Measure are the result of above formulas and next to each of them is the column from the sample data you had provided.

Upvote and accept as a solution if it helped!

[vibhoryadav23]

Thanks @govindarajan_d
It doesnt work if there's a month missing in the order. Example: Observe that in first 2 rows, 202402 is missing (2024 February)

(I have clubbed year and month to from 'Period' accomodate historic data)

I have added an example of a user in the original post for more clarity

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a measure.

WINDOW function (DAX) - DAX | Microsoft Learn

Present all in last 3 months: = 
IF (
    COUNTROWS (
        WINDOW (
            -2,
            REL,
            0,
            REL,
            SUMMARIZE ( ALL ( Data ), Months[Months], Users[Users] ),
            ORDERBY ( Months[Months], ASC ),
            ,
            PARTITIONBY ( Users[Users] )
        )
    ) >= 3,
    1,
    0
)

Present 2 in last 3 months: = 
IF (
    COUNTROWS (
        WINDOW (
            -2,
            REL,
            0,
            REL,
            SUMMARIZE ( ALL ( Data ), Months[Months], Users[Users] ),
            ORDERBY ( Months[Months], ASC ),
            ,
            PARTITIONBY ( Users[Users] )
        )
    ) >= 2,
    1,
    0
)

[vibhoryadav23]

@Jihwan_Kim Thanks for the effort. This doesnt work unfortunately.

I have added an example of a user in the original post for more clarity.