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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
unusdusrname
Frequent Visitor

How to calculate delta between cumulative values

Hello daxperts, I would very much appreciate some help before I pull my remaining hair out 🙂

 

I have some sales data which are published only as cumulative numbers, and include a number of other descriptors for region, client, order type. Sample below.

 

What I need to derive dynamically is the delta between the cumulative value at datestamp and datestamp minus one, allowing me to filter the vis by region, customer, bookings type.

 

I was able to achieve this by rendering my data as a pivot in excel and summarising booked by difference from->datestamp->previous, then manually copying the region/client/type data into these new records, but my dataset grows significantly and the process is so manual.

 

Can this be done in DAX?

 

Thanks for reading.

SALES_LEVEL_1DatestampEND CUSTOMER NAMEBookings TypeBooked
APJC24Q1wk02JimSOFTWARE18758
APJC24Q1wk02DaveSOFTWARE-9
APJC24Q1wk02JimHARDWARE26651
APJC24Q1wk02DaveSERVICE-38
APJC24Q1wk02JimSERVICE7712
APJC24Q1wk03JimHARDWARE26689
APJC24Q1wk03JimSOFTWARE18758
APJC24Q1wk03DaveHARDWARE32694
APJC24Q1wk03DaveSERVICE-38
APJC24Q1wk03DaveSOFTWARE669
APJC24Q1wk03JimSERVICE-443207
APJC24Q1wk04JimHARDWARE26689
APJC24Q1wk04JimSOFTWARE18758
APJC24Q1wk04JimSERVICE-443207
APJC24Q1wk04DaveSERVICE-38
APJC24Q1wk04DaveHARDWARE32694
APJC24Q1wk04DaveSOFTWARE669
APJC24Q1wk05DaveSOFTWARE2520
APJC24Q1wk05JimHARDWARE26689
APJC24Q1wk05JimSOFTWARE18758
APJC24Q1wk05DaveSERVICE-38
APJC24Q1wk05JimSERVICE-443207
APJC24Q1wk05DaveHARDWARE51142
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_0-1710303130925.png

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

Delta Booked value CC =
VAR _condition =
    COUNTROWS (
        OFFSET (
            -1,
            Data,
            ORDERBY ( Data[Datestamp], ASC ),
            ,
            PARTITIONBY (
                Data[SALES_LEVEL_1],
                Data[END CUSTOMER NAME],
                Data[Bookings Type]
            ),
            MATCHBY (
                Data[SALES_LEVEL_1],
                Data[Datestamp],
                Data[END CUSTOMER NAME],
                Data[Bookings Type]
            )
        )
    )
        <> BLANK ()
RETURN
    IF (
        _condition,
        Data[Booked]
            - SUMX (
                OFFSET (
                    -1,
                    Data,
                    ORDERBY ( Data[Datestamp], ASC ),
                    ,
                    PARTITIONBY (
                        Data[SALES_LEVEL_1],
                        Data[END CUSTOMER NAME],
                        Data[Bookings Type]
                    ),
                    MATCHBY (
                        Data[SALES_LEVEL_1],
                        Data[Datestamp],
                        Data[END CUSTOMER NAME],
                        Data[Bookings Type]
                    )
                ),
                Data[Booked]
            )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_0-1710303130925.png

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

Delta Booked value CC =
VAR _condition =
    COUNTROWS (
        OFFSET (
            -1,
            Data,
            ORDERBY ( Data[Datestamp], ASC ),
            ,
            PARTITIONBY (
                Data[SALES_LEVEL_1],
                Data[END CUSTOMER NAME],
                Data[Bookings Type]
            ),
            MATCHBY (
                Data[SALES_LEVEL_1],
                Data[Datestamp],
                Data[END CUSTOMER NAME],
                Data[Bookings Type]
            )
        )
    )
        <> BLANK ()
RETURN
    IF (
        _condition,
        Data[Booked]
            - SUMX (
                OFFSET (
                    -1,
                    Data,
                    ORDERBY ( Data[Datestamp], ASC ),
                    ,
                    PARTITIONBY (
                        Data[SALES_LEVEL_1],
                        Data[END CUSTOMER NAME],
                        Data[Bookings Type]
                    ),
                    MATCHBY (
                        Data[SALES_LEVEL_1],
                        Data[Datestamp],
                        Data[END CUSTOMER NAME],
                        Data[Bookings Type]
                    )
                ),
                Data[Booked]
            )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Actually, though it's close, I'm not quite getting the answers I'd expected. I think it has to do with whether a given combination of client/region/type existed in the previous week. If not we should return the total value as the delta, but I think we're getting nothing. I'm trying to modify that now

 

Brilliant. Thanks so much 😁 👍

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.