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
alextf94
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:

 

alextf94_0-1713567340297.png

 

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
1 ACCEPTED SOLUTION
alextf94
New Member

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



View solution in original post

6 REPLIES 6
alextf94
New Member

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
New Member

Hello @DataNinja777 !

It doesnt work! 

alextf94_0-1713621316879.png


It only changed the numbers and I think wrongly.

alextf94_1-1713621395428.png


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

Do you know what i am doing wrong?

 

 

 

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])

DataNinja777_0-1713626305322.png

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

alextf94_0-1713619154007.png

 

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_1-1713619305197.png

 



DataNinja777
Super User
Super User

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,

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.  

DataNinja777_1-1713620051643.png

Best regards,

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.