Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bokazoit
Responsive Resident
Responsive Resident

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

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 🙂

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

 

Bokazoit_0-1718613133542.png


The joins are as shown:

Bokazoit_1-1718613174756.png


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:

Bokazoit_2-1718613466593.png

 

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?

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

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.

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors