Forum Discussion
Summerize rows differences in one table
- 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.
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 .
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
- edhans5 years ago
Community Champion
Great Anonymous ! Glad I was able to help.