cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Summerize rows differences in one table

Hi,

 

Each month from my CRM I got an extract of my current customers that I aggregate on a single table:

 

 

 

 

name		creationDate	sales	Date
Customer 11	12/04/2021		500		avr-21
Customer 10	19/01/2021		600		avr-21
Customer 9	16/11/2020		600		avr-21
Customer 7	12/11/2020		500		avr-21
Customer 6	14/09/2020		300		avr-21
Customer 6	14/09/2020		600		avr-21
Customer 5	31/08/2020		200		avr-21
Customer 5	31/08/2020		200		avr-21
Customer 4	10/02/2021		600		avr-21
Customer 4	17/10/2019		400		avr-21
Customer 3	12/03/2019		450		avr-21
Customer 3	08/03/2019		450		avr-21
Customer 12	12/05/2021		600		mai-21
Customer 11	12/04/2020		500		mai-21
Customer 10	19/01/2021		300		mai-21
Customer 9	16/11/2020		600		mai-21
Customer 8	12/11/2020		500		mai-21
Customer 7	10/05/2021		500		mai-21
Customer 7	12/11/2020		500		mai-21
Customer 6	14/09/2020		600		mai-21
Customer 4	10/02/2021		600		mai-21
Customer 3	12/03/2019		600		mai-21
Customer 3	08/03/2019		450		mai-21

 

 

 

 

I need to identify the news business and too the lost business like that:

 

 

 

 

name			sales
Customer 12		600
Customer 10		-300
Customer 8		500
Customer 7		500
Customer 6		-300
Customer 5		-400
Customer 4		-400
Customer 3		150

 

 

 

 

using 

MTD Sales = CALCULATE(SUM(table[sales]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(table[sales]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

I manage to have

 

 

 

name			sales
Customer 12		600
Customer 10		-300
Customer 8		500
Customer 7		500
Customer 6		-300
Customer 4		-400
Customer 3		150

 

 

 

 

but the line Customer 5 is missing (was customer on april but not anymore in may)

 

My question: how can I get the line Customer 5 included with the others rows?

 

Thanks

 

 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

This measure will return this table. You cannot get a table like you are showing above using the MTD values because your visual has no dates, so nothng for DatesMTD to work on.

edhans_0-1624747171989.png

Sales Difference = 
VAR varLastDate = 
    CALCULATE(
        MAX(Sales[Date]),
        ALLEXCEPT(Sales,'Date')
    )
VAR varPreviousDate = EOMONTH(varLastDate,-1)
VAR varCurrentDates = 
    FILTER(
        ALL('Date'[Date],'Date'[Month End]),
        'Date'[Month End] = varLastDate
    )
VAR varPreviousDates = 
    FILTER(
        ALL('Date'[Date],'Date'[Month End]),
        'Date'[Month End] = varPreviousDate
    )
VAR varCurrentSales = 
    CALCULATE(
        [Total Sales],
        varCurrentDates
    )
VAR varPreviousSales = 
    CALCULATE(
        [Total Sales],
        varPreviousDates
    )
VAR Result = varCurrentSales - varPreviousSales
RETURN
    Result

 

You can see my PBIX here with the date table I am using


If that is not what you want, please provide more info, but DATESMTD() requires a date in the filter context to operate on, and the result table you show above has no date in it.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

This measure will return this table. You cannot get a table like you are showing above using the MTD values because your visual has no dates, so nothng for DatesMTD to work on.

edhans_0-1624747171989.png

Sales Difference = 
VAR varLastDate = 
    CALCULATE(
        MAX(Sales[Date]),
        ALLEXCEPT(Sales,'Date')
    )
VAR varPreviousDate = EOMONTH(varLastDate,-1)
VAR varCurrentDates = 
    FILTER(
        ALL('Date'[Date],'Date'[Month End]),
        'Date'[Month End] = varLastDate
    )
VAR varPreviousDates = 
    FILTER(
        ALL('Date'[Date],'Date'[Month End]),
        'Date'[Month End] = varPreviousDate
    )
VAR varCurrentSales = 
    CALCULATE(
        [Total Sales],
        varCurrentDates
    )
VAR varPreviousSales = 
    CALCULATE(
        [Total Sales],
        varPreviousDates
    )
VAR Result = varCurrentSales - varPreviousSales
RETURN
    Result

 

You can see my PBIX here with the date table I am using


If that is not what you want, please provide more info, but DATESMTD() requires a date in the filter context to operate on, and the result table you show above has no date in it.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks very much for this. Is working on the sample data (fake) but not in the true one, and so far I don't undestand why. May be is linked to a bad explanation on my side of the sales lines. To try to be clear the lines in may are not necessary new income and most of the time they are pre-existing income .

 

Total Sales = SUM(Sales[Sales]) is wrong in a way is not possible to SUM several time the same lines presents in april and may. For instance the total sales for Customer 3 in may is 1050 and in april 900. The difference is 150 between this 2 months but display 1950 Total sales is not exact. 
 
Hope to be understood and I think I me very close to the solution.
 
Thanka again
Anonymous
Not applicable

undestood my date field was the first day on the month ==> I just transform it with EMONTH(DATE,-1) and it runs  like  a charm ^^

 

Thanks

Great @Anonymous ! Glad I was able to help.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

The total sales was just me adding data together. You can remove that from the visual. the difference is accurate.

 

If you want help you need to be very clear on what you want. If you get rid of my [Total Sales] measure from the visual, it returns the 2 column visual you had above, the customer, and the difference. Otherwise, please read the instructions below. Remember, I know nothing about your data, so explan clearly.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.