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 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?

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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

     

    • vibhoryadav23's avatar
      vibhoryadav23
      Helper II
      2 years ago

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

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