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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
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