Forum Discussion
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 dropped out (in Power BI).
Dummy data:
Basic Table
And what I would like is a table that gave an overview month-to-month how many was signed up when the month started and how did the total change at the end of the month.
What I'm really struggling to figure out is how to get Power BI to evaluate this in the right sequence.
Desired table
I hope someone can help me!
Thanks,
Hans
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+0Sign 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
6 Replies
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+0Sign 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
hans_j_h , last month dropped out is your this month opening
last MTD Sales = CALCULATE([dropped out]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
