topic Re: How to calculate this using measure only? (sample file attached) in DAX Commands and Tips

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

Bokazoit — Mon, 17 Jun 2024 10:35:01 GMT

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?

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

rajendraongole1 — Mon, 17 Jun 2024 09:06:40 GMT

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!!

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

Bokazoit — Mon, 17 Jun 2024 09:14:36 GMT

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

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

rajendraongole1 — Mon, 17 Jun 2024 09:28:44 GMT

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!!

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

Bokazoit — Mon, 17 Jun 2024 09:29:40 GMT

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?

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

rajendraongole1 — Mon, 17 Jun 2024 09:37:08 GMT

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!!

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

Bokazoit — Mon, 17 Jun 2024 09:57:06 GMT

Sorry I need it to add subscription too

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

Bokazoit — Mon, 17 Jun 2024 10:00:40 GMT

It needs to look like this:

Your result is test and the correct is without the test

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

Bokazoit — Mon, 17 Jun 2024 10:13:25 GMT

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)

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

Bokazoit — Mon, 17 Jun 2024 10:35:59 GMT

Up, I would really like this to work 🙁

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

Anonymous — Mon, 24 Jun 2024 01:46:39 GMT

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