Forum Discussion
Formulae for repeat usage across rows
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 |
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
11 Replies
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!
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
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 __ResultAlternate 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 __ResultGreg_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.
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.
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 )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.
