How to calculate this using measure only? (sample file attached)

[Bokazoit]

I have this data:
Please use the sample file to see the effect of the dateslider.

https://drive.google.com/file/d/1SlKQobvylDYylkmB5YO2UOZ8CxK_HrpL/view?usp=sharing 

The joins are as shown:

In my example I created a column called FirstSubscription which is the first date when then bought the Category = "subscription"

So for customer 123 it is 30. june 2023 and 456 it is 12. january 2023.

I then sum all categories that were bought before that time respectively after that date with the category "Subscription" included. That gives me these results:

Using these measures:

SumAmountAfter = 

    CALCULATE(
        SUM(Tabel[Amount]),
        USERELATIONSHIP(DimDato[Date],Tabel[FirstSubscription]),
        Tabel[DatoKeyStoettePeriode] >= Tabel[FirstSubscription]
    )

SumAmountBefore = 

    CALCULATE(
        SUM(Tabel[Amount]),
        USERELATIONSHIP(DimDato[Date],Tabel[FirstSubscription]),
        Tabel[FirstSubscription] >= Tabel[DatoKeyStoettePeriode]
    )

My real table consists of 7 mio. lines and I would prefer to avoid adding a column with the FirstSubscription date that is in my sample file. 

So my question is, can I do this without having the column FirstSubscription and instead calculate for each customer the FirstSubscription date and use it in my measures above?

[Bokazoit]

Up, I would really like this to work 🙁

[Anonymous]

Hi @Bokazoit,

Have you tried to add a variable table with summarize function and add the custom fields with your calculation formula to get the count of records in the virtual table.
After these steps, you can use iterator function to summary this variable table.

Measure Totals, The Final Word  

Regards,

Xiaoxin Sheng

[rajendraongole1]

Hi @Bokazoit - you can try to create calculate the FirstSubscription date dynamically within your measures. using MINX function to find the first subscription date for each customer as below:

Create below measure for SumAmountAfter

SumAmountAfter =
CALCULATE(
SUM(Tabel[Amount]),
Tabel[DatoKeyStoettePeriode] >=
CALCULATE(
MINX(
FILTER(
Tabel,
Tabel[Category] = "subscription"
),
Tabel[DatoKeyStoettePeriode]
),
ALLEXCEPT(Tabel, Tabel[CustomerID])
)
)

Create another measure for sumamount before:

SumAmountBefore =
CALCULATE(
SUM(Tabel[Amount]),
Tabel[DatoKeyStoettePeriode] <=
CALCULATE(
MINX(
FILTER(
Tabel,
Tabel[Category] = "subscription"
),
Tabel[DatoKeyStoettePeriode]
),
ALLEXCEPT(Tabel, Tabel[CustomerID])
)
)

Try the above measures for both after and before with category= subscription condition and lets see.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Bokazoit]

Could You try it in my samplefile? Your suggestion gave an error:

[rajendraongole1]

@Bokazoit - try the below logic, it is working snapshot for your refereence.

can you try below one.

SumAmountAfter =
VAR FirstSubscriptionDate =
CALCULATE(
MINX(
FILTER(
Tabel,
Tabel[Category] = "subscription"
),
Tabel[DatoKeyStoettePeriode]
),
ALLEXCEPT(Tabel, Tabel[CustomerID])
)
RETURN
CALCULATE(
SUM(Tabel[Amount]),
Tabel[DatoKeyStoettePeriode] >= FirstSubscriptionDate
)

SumAmountBefore measure 2:

SumAmountBefore =
VAR FirstSubscriptionDate =
CALCULATE(
MINX(
FILTER(
Tabel,
Tabel[Category] = "subscription"
),
Tabel[DatoKeyStoettePeriode]
),
ALLEXCEPT(Tabel, Tabel[CustomerID])
)
RETURN
CALCULATE(
SUM(Tabel[Amount]),
Tabel[DatoKeyStoettePeriode] <= FirstSubscriptionDate
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Bokazoit]

Tx for trying, but the result is not correct:

It is missing the subscription that should say 2, and when I move the date slider it does not count correctly. Have You tried using my samplefile?

[rajendraongole1]

Hi @Bokazoit -- Can you please check the below snapshot  added to that concatenation pipeline with subscription. 

try below measure:

SumAmountAfter =

VAR FirstSubscriptionDate =

    CALCULATE(

        MINX(

            FILTER(

                Tabel,

                Tabel[Category] = "subscription"

            ),

            Tabel[DatoKeyStoettePeriode]

        ),

        ALLEXCEPT(Tabel, Tabel[Customer])

    )

RETURN

CALCULATE(

    SUM(Tabel[Amount]),

    FILTER(

        Tabel,

        Tabel[DatoKeyStoettePeriode] >= FirstSubscriptionDate || Tabel[Category] = "subscription"

    )

)

Measure:2

SumAmountBefore =

VAR FirstSubscriptionDate =

    CALCULATE(

        MINX(

            FILTER(

                Tabel,

                Tabel[Category] = "subscription"

            ),

            Tabel[DatoKeyStoettePeriode]

        ),

        ALLEXCEPT(Tabel, Tabel[Customer])

    )

RETURN

CALCULATE(

    SUM(Tabel[Amount]),

    FILTER(

        Tabel,

        Tabel[DatoKeyStoettePeriode] <= FirstSubscriptionDate && Tabel[Category] <> "subscription"

    )

)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Bokazoit]

I can try to add this view:

The (test) is Your measures. If I move the date slider it should resemble the tables with the measure without the (test)

[Bokazoit]

It needs to look like this:

Your result is test and the correct is without the test

[Bokazoit]

Sorry I need it to add subscription too