Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
reneM2026
Frequent Visitor

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

 

reneM2026_1-1695387433905.png

 

 

 

1 ACCEPTED SOLUTION
reneM2026
Frequent Visitor

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)
reneM2026_0-1695397272762.png

 

View solution in original post

1 REPLY 1
reneM2026
Frequent Visitor

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)
reneM2026_0-1695397272762.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.