Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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 .
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |