Forum Discussion

amigi's avatar
amigi
New Member
9 years ago

Conditioned count per user

Hi all,

I have a table similar to the example below.

I would like to claculate the number of users that had at least 2 days of transactions in the last 3 days.

in the example below the value for march 17th would be 2

 

This is what I'm using so far (to calculate number of users that had at least 1 day of transaction/s):

rolling3daysoftransactions =
CALCULATE (
CALCULATE(DISTINCTCOUNT(Table[UserID]), Table[numberoftransactions] <> 0 ),
DATESINPERIOD ( Table[Date], LASTDATE ( Table[Date] ), -7, DAY )
)

 

(assuming the above correct at all) I'm struggleing with doing the same for my original intent (claculate the number of users that had at least 2 days of transactions in the last 3 days)

 

Any ideas?

Thanks!

 

 

UserIDDate number of transactions 
a15-Mar0
b15-Mar7
c15-Mar2
a16-Mar0
b16-Mar2
c16-Mar4
a17-Mar0
b17-Mar0
c17-Mar9

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi amigi，

    Firstly, create a calculated column using formula below.

    checktrans = IF(Table[number of transactions ]=0, 0,1)

    Secondly, create the following measures and create a table visual as shown in the screenshot below, then check if it returns your expected result.

    rolling trans per user = CALCULATE(SUM(Table[checktrans]),FILTER(ALL(Table),Table[Date]<=max(Table[Date])),VALUES('Table'[UserID]))

    Measure = CALCULATE(COUNTA(Table[UserID]),FILTER(Table,Table[rolling trans per user]>=2))



     

    Thanks,
    Lydia Zhang

    • amigi's avatar
      amigi
      New Member
      9 years ago

      Thansk Lydia (Anonymous)!

       

      I think that it indeed helps with the "groupby" functianlity, however it seems like it misses the "last 3 days" condition. (claculate the number of users that had at least 2 days of transactions in the last 3 days).    right?

       

      I assume that this condition should be expressed here?

      rolling trans per user = CALCULATE(SUM(Table[checktrans]),FILTER(ALL(Table),Table[Date]<=max(Table[Date])),VALUES('Table'[UserID]))

       

      but I can't understand this filter expression. 

      could you please help me in understanding it?  and how to express the "in the last X days" condition?

       

      Thanks!

      Igi

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi amigi,

        You only want to get data of March 17, right? If so, create a column using  the DAX below and use it to filter your visual.

        Column = IF(Table[Date ]=TODAY()-5,1,0)



        Thanks,
        Lydia Zhang