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

     

govindarajan_d's avatar
govindarajan_d
Super User
2 years ago

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

    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