Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average days

Hi everyone !

I have some troubles with my data, this is what I have:

Phone Date OperationType Amount
222 01/01/2020 14:00 Balance 250
223 01/01/2020 14:00 Payment 10
222 03/01/2020 14:00 Other 10
225 01/01/2020 14:00 Balance 20
225 02/01/2020 14:00 Other 10
225 01/01/2020 14:00 Balance 5
222 04/01/2020 14:00 Payment 50


1. Phone is like the id for each user
2. Date is the timestamp of the operation
3. There are three type of operations: as Balance(when someone buy balance in app), Payment(when someone use their balance to pay) and other operations
4. Amount

What I want is to create a column with the average days since a user has a balance operations to the other two.
For example


Phone Date OperationType Amount AvgDays
222 01/01/2020 14:00 Balance 250 2
223 01/01/2020 14:00 Payment 10 0
222 03/01/2020 14:00 Payment 10 2
225 01/01/2020 14:00 Balance 20 3.5
225 02/01/2020 14:00 Other 10 3.5
225 04/01/2020 14:00 Balance 5 3.5
225 10/01/2020 14:00 Payment 50 3.5

 

User 222 has a balance operations on jan 01, then the user use his balance to pay on jan 3 (2 days). User 225 has a balance operation on jan 1, then an operation on jan 2 (1 day), the same user has another balance operation in jan 4 and use his balance to pay on jan 10 (6 days), the average days for the user is : (1+6)/ = 3.5

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You can try my calculated column.

     

    Rank = 
IF (
    'Table'[OperationType] = "Balance",
    RANKX (
        FILTER (
            'Table',
            'Table'[OperationType] = "Balance"
                && 'Table'[Phone] = EARLIER ( 'Table'[Phone] )
        ),
        'Table'[Date],
        ,
        ASC
    ),
    RANKX (
        FILTER (
            'Table',
            'Table'[OperationType] <> "Balance"
                && 'Table'[Phone] = EARLIER ( 'Table'[Phone] )
        ),
        'Table'[Date],
        ,
        ASC
    )
)
    DateDiff = 
DATEDIFF('Table'[Date],CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Phone]=EARLIER('Table'[Phone])&&'Table'[Rank]=EARLIER('Table'[Rank]))),DAY)
    AvgDays =
VAR _MAXRank =
    MAXX (
        FILTER ( 'Table', 'Table'[Phone] = EARLIER ( 'Table'[Phone] ) ),
        'Table'[Rank]
    )
VAR _AvgDays =
    IF (
        'Table'[Phone] = 'Table'[Phone]
            && 'Table'[Rank] = 'Table'[Rank],
        CALCULATE (
            SUM ( 'Table'[DateDiff] ),
            FILTER (
                'Table',
                'Table'[Phone] = EARLIER ( 'Table'[Phone] )
                    && 'Table'[OperationType] = "Balance"
            )
        )
    ) / _MAXRank
RETURN
    IF ( ISBLANK ( _AvgDays ), 0, _AvgDays )

     

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

     

    You should be able to create a column that lists the days since pay and balance information and then you could use the default Average aggregation in a visualization for that column or create a second column that does the average.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You can try my calculated column.

     

    Rank = 
IF (
    'Table'[OperationType] = "Balance",
    RANKX (
        FILTER (
            'Table',
            'Table'[OperationType] = "Balance"
                && 'Table'[Phone] = EARLIER ( 'Table'[Phone] )
        ),
        'Table'[Date],
        ,
        ASC
    ),
    RANKX (
        FILTER (
            'Table',
            'Table'[OperationType] <> "Balance"
                && 'Table'[Phone] = EARLIER ( 'Table'[Phone] )
        ),
        'Table'[Date],
        ,
        ASC
    )
)
    DateDiff = 
DATEDIFF('Table'[Date],CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Phone]=EARLIER('Table'[Phone])&&'Table'[Rank]=EARLIER('Table'[Rank]))),DAY)
    AvgDays =
VAR _MAXRank =
    MAXX (
        FILTER ( 'Table', 'Table'[Phone] = EARLIER ( 'Table'[Phone] ) ),
        'Table'[Rank]
    )
VAR _AvgDays =
    IF (
        'Table'[Phone] = 'Table'[Phone]
            && 'Table'[Rank] = 'Table'[Rank],
        CALCULATE (
            SUM ( 'Table'[DateDiff] ),
            FILTER (
                'Table',
                'Table'[Phone] = EARLIER ( 'Table'[Phone] )
                    && 'Table'[OperationType] = "Balance"
            )
        )
    ) / _MAXRank
RETURN
    IF ( ISBLANK ( _AvgDays ), 0, _AvgDays )

     

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.