Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Accumulated difference

I have a table like the one below which has the accumulated sales per month, I need to calculate the real value of the month considering that there are several companies that have different branches ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I managed to do it this way. First create a column that has de previous value of sales.

     

    Previous Sale =
    VAR PreviousDate = DATEADD(BASE[Date], -1, MONTH) 
    RETURN
    IF(BASE[NUM_MONTH] = 1, 0,
        SUMX(
            FILTER(
                'BASE',
                'BASE'[DATE] = PreviousDate &&
                BASE[Company] = EARLIER(BASE[Company]) &&
                BASE[Branch] = EARLIER(BASE[Branch])
            ),
            BASE[Sales]
        )
    )
     
    Then I used a metric like the following.
    Monthly Sales = SUM(BASE[Sales]) - SUM(BASE[Previous Sale])
     
    That way i managed to get the monthly Sales by company and branches (Corredora = Company, Produccion = Sales, Produccion Mensual = Monthly Sales)

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I managed to do it this way. First create a column that has de previous value of sales.

 

Previous Sale =
VAR PreviousDate = DATEADD(BASE[Date], -1, MONTH) 
RETURN
IF(BASE[NUM_MONTH] = 1, 0,
    SUMX(
        FILTER(
            'BASE',
            'BASE'[DATE] = PreviousDate &&
            BASE[Company] = EARLIER(BASE[Company]) &&
            BASE[Branch] = EARLIER(BASE[Branch])
        ),
        BASE[Sales]
    )
)
 
Then I used a metric like the following.
Monthly Sales = SUM(BASE[Sales]) - SUM(BASE[Previous Sale])
 
That way i managed to get the monthly Sales by company and branches (Corredora = Company, Produccion = Sales, Produccion Mensual = Monthly Sales)