Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Helper II
2 years ago
Solved

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 in...
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
    vibhoryadav23
    Helper II
    2 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      2 years ago

      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's avatar
        vibhoryadav23
        Helper II
        2 years ago

        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?