Forum Discussion

alextf94's avatar
alextf94
New Member
2 years ago
Solved

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?

Thanks in advance,

Alex
  • alextf94's avatar
    alextf94
    2 years ago

    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



6 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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's avatar
      DataNinja777
      Super User
      2 years ago

      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,

       

  • alextf94's avatar
    alextf94
    New Member
    2 years ago

    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:


     



  • alextf94's avatar
    alextf94
    New Member
    2 years ago

    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's avatar
      DataNinja777
      Super User
      2 years ago

      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's avatar
    alextf94
    New Member
    2 years ago

    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