Calculate amount depending on customer and customer purchase date (Sample file linked)

[Bokazoit]

Sorry, I marked it as solved and the post was moved to DAX commands and tips. But it was not solved. Please use the sample file to see the effect of the dateslider when trying to help with the measures 🙂

@Member123456 plz delete the other post in 'DAX commands and tips' since it is not solved yet.

I have this data:

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?

[Anonymous]

Hi @Bokazoit 

If you do not have a FirstSubscription date column in your data, I recommend that you create a new column for FirstSubscription date.

This is the simplest way that you can use your original code to achieve the goal.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bokazoit]

I know that, I was looking for a solution without having to populate a tabel with that only information