cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## SUMX filtered with ALL previously

Hello everyone.

I am having a problem with a DAX query here...

Basically, i need to sum the first historical sale of each client for only 3 months, then i need to go blank for the next months upcoming. Like this:

The problem is that in the totals, it not keep summing after the 3 months.

What i did was:

Sales 3 months after first historical sale =

VAR StartDate = CALCULATE(MIN(Sales[Date]),FILTER(ALLSELECTED(Calendario[Date]),[Sales]>0))
VAR EndDate = EOMONTH(DATE( YEAR( StartDate), MONTH(StartDate)+3, 1),0)
RETURN
SUMX(FILTER(Sales,Sales[Date]>=StartDate && Sales[Date] <= EndDate),[Sales])

Does anyone knows how can i resolve this issue?

Alex
1 ACCEPTED SOLUTION
New Member

Hello Everyone, Chat GPT helped me 🙂

If anyone needs this solution please be my guest:

``````VAR Alta_Por_Cliente =
SUMMARIZE('Stores', 'Stores'[Cust_ID String]),
"Fecha Alta", CALCULATE(FIRSTDATE('Stores'[Fecha]), FILTER(ALL(Calendario[Date]),[\$ TPV Financing post cierre]>0))
)
VAR Tres_Meses_After_Alta =
Alta_Por_Cliente,
"Tres Meses Después", EOMONTH([Fecha Alta], 3)
)
RETURN
SUMX(
Tres_Meses_After_Alta,
VAR ClienteActual = 'Stores'[Cust_ID String]
VAR FechaAlta = [Fecha Alta]
VAR TresMesesDespues = [Tres Meses Después]
RETURN
CALCULATE(
[\$ TPV Financing post cierre],
'Stores'[Cust_ID String] = ClienteActual &&
'Stores'[Fecha] >= FechaAlta &&
'Stores'[Fecha] <= TresMesesDespues
)
)``````

Table Stores is my sales/Fact table in this case and TPV Financed is my sales metric

6 REPLIES 6
New Member

Hello Everyone, Chat GPT helped me 🙂

If anyone needs this solution please be my guest:

``````VAR Alta_Por_Cliente =
SUMMARIZE('Stores', 'Stores'[Cust_ID String]),
"Fecha Alta", CALCULATE(FIRSTDATE('Stores'[Fecha]), FILTER(ALL(Calendario[Date]),[\$ TPV Financing post cierre]>0))
)
VAR Tres_Meses_After_Alta =
Alta_Por_Cliente,
"Tres Meses Después", EOMONTH([Fecha Alta], 3)
)
RETURN
SUMX(
Tres_Meses_After_Alta,
VAR ClienteActual = 'Stores'[Cust_ID String]
VAR FechaAlta = [Fecha Alta]
VAR TresMesesDespues = [Tres Meses Después]
RETURN
CALCULATE(
[\$ TPV Financing post cierre],
'Stores'[Cust_ID String] = ClienteActual &&
'Stores'[Fecha] >= FechaAlta &&
'Stores'[Fecha] <= TresMesesDespues
)
)``````

Table Stores is my sales/Fact table in this case and TPV Financed is my sales metric

New Member

Hello @DataNinja777 !

It doesnt work!

It only changed the numbers and I think wrongly.

CUST is my Clients data dim and Stores is my fact/sales table.

Do you know what i am doing wrong?

Super User

Thanks for your feedback.  I did further testing using the dummy data I created to mimic your data, and found out that if you create a separate measure which references your original measure and sumxing over the customer table, it produces your required output.  I cannot explain why this separate measure approach works while the combined measure approach didn't work.  The 1st table is your original measure's behavior, and the 2nd measure is the sumxing of your original measure in a separate measure.

``Sumxing over customer table = sumx(CUST,[Sales 3 months after first historical sale])``

If sumxing over the entire Customer table doesn't work, please try values(Cust[Customer]).  I cannot explain the technical intricacies of why it works and the other doesn't work, but you can get your required output in the manner above.

New Member

Hello @DataNinja777 !

First of all thank you for the response! The issue is that if i whant to put the same metric in a Line Chart, it gets me to this:

Thats why i think the issue is with the totals of the same table i posted before! When i said totals i was referring to this:

Super User

Could you clarify what is the issue?   You total is only showing the first 3 months for each customer and your visual shows the correct summation of the visual over time dimension.  Do you consider this as an issue?

Best regards,

Super User

Thank you for your clarification.  I am assuming that your Customer ID is from your customer master data dimension table which has relationship with your fact table.  I think Sumxing over customer table will solve your problem of not correctly summing over all the customers, and just suming over the first 3 months' customers.

Best regards,

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors