Evaluating in sequences

[hans_j_h]

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

[mahoneypat · Accepted solution]

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

[amitchandak]

@hans_j_h , last month dropped out is your this month opening

last MTD Sales = CALCULATE([dropped out]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

[Anonymous]

@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]

Hi @Anonymous 

Thanks for your quick answer!

Could you elaborate? What DAX codes should I use?

[Anonymous]

@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]

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.