Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • edhans's avatar
    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
        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.