SUMX filtered with ALL previously

[alextf94]

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?

Thanks in advance,

Alex

[alextf94 · Accepted solution]

Hello Everyone, Chat GPT helped me 🙂

If anyone needs this solution please be my guest:

VAR Alta_Por_Cliente =
    ADDCOLUMNS(
        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 =
    ADDCOLUMNS(
        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

[alextf94]

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?

[DataNinja777]

Hi @alextf94 

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.  

[alextf94]

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:

[DataNinja777]

Hi @alextf94 

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,

[DataNinja777]

Hi @alextf94 

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,