Forum Discussion

hans_j_h's avatar
hans_j_h
New Member
6 years ago
Solved

Evaluating in sequences

Hi all,   Sorry if this question has been answered before. But I struggle to find any answers.   My issue is, that I have a table showing me for each month how many signed up as well as how dropp...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    I was working on this at same time must be, and didn't see last reply.  In any case, here is another way that results in this table (I only put 3 mos of example data in).

     

    Here are the measures:  The opening value is just the sum of all the prev sign ups minus sum of prev dropouts.

    Opening Value = var mindate = MIN(Sequences[Date])
    var signups = CALCULATE(SUM(Sequences[Value]), FILTER(ALL(Sequences), Sequences[Date]<mindate), Sequences[Measure]="Signed in")
    var dropouts = CALCULATE(SUM(Sequences[Value]), FILTER(ALL(Sequences), Sequences[Date]<mindate), Sequences[Measure]="Dropped out")
    return signups-dropouts+0
     
    Sign Up = CALCULATE(SUM(Sequences[Value]), Sequences[Measure]="Signed in")
     
    Dropped out = CALCULATE(SUM(Sequences[Value]), Sequences[Measure]="Dropped out")
     
    Closing Value = [Opening Value]+[Sign Up]-[Dropped out]
     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

hans_j_h 

Well its ugly but it works...Make measures 3 & 4 first.

 

1) One for start of month running total =

MonthStartTotal =
CALCULATE (
    [Signed In],
    FILTER (
        ALLSELECTED ( 'Table'[MonthYear] ),
        ISONORAFTER ( 'Table'[MonthYear] + 1, MAX ( 'Table'[MonthYear] ), DESC )
    )
)
    + CALCULATE (
        [Dropped Out],
        FILTER (
            ALLSELECTED ( 'Table'[MonthYear] ),
            ISONORAFTER ( 'Table'[MonthYear] + 1, MAX ( 'Table'[MonthYear] ), DESC )
        )
    ) + 0

 

2) One for end of month running total

 

MonthEndTotal =
CALCULATE (
    [Signed In],
    FILTER (
        ALLSELECTED ( 'Table'[MonthYear] ),
        ISONORAFTER ( 'Table'[MonthYear], MAX ( 'Table'[MonthYear] ), DESC )
    )
)
    + CALCULATE (
        [Dropped Out],
        FILTER (
            ALLSELECTED ( 'Table'[MonthYear] ),
            ISONORAFTER ( 'Table'[MonthYear], MAX ( 'Table'[MonthYear] ), DESC )
        )
    )

3) One for increases

Signed In = CALCULATE(SUM('Table'[Value]),'Table'[Measure]="Signed In")

4) one for decreases

Dropped Out = CALCULATE(SUM('Table'[Value]),'Table'[Measure]="Dropped Out")*-1
hans_j_h's avatar
hans_j_h
New Member
6 years ago

Thanks for the answer, Anonymous.

 

It works! Although, I will accept mahoneypat answer as the solution as this solution is a little more straight forward.