Forum Discussion
Anonymous
5 years agoNot 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...
- 5 years ago
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 ResultYou 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.
Anonymous
5 years agoNot 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
edhans
5 years agoCommunity Champion
Great Anonymous ! Glad I was able to help.