Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.