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_hYou will need to create four measures.

1) One for start of month running total.

2) One for end of month running total.

3) One for increases.

4) one for decreases.

 

 

 

hans_j_h's avatar
hans_j_h
New Member
6 years ago

Hi Anonymous 

 

Thanks for your quick answer!

 

Could you elaborate? What DAX codes should I use?

  • 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.