Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 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 and that each one can belong to a different category.

 

Another option could be to create a column with the previous data and then perform the subtraction but I also have problems with that.

 

Consider that the real table has more companies, branches and goes from 1/2019 to 7/2023

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 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)

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 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)