How to calculate delta between cumulative values

[unusdusrname]

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_1	Datestamp	END CUSTOMER NAME	Bookings Type	Booked
APJC	24Q1wk02	Jim	SOFTWARE	18758
APJC	24Q1wk02	Dave	SOFTWARE	-9
APJC	24Q1wk02	Jim	HARDWARE	26651
APJC	24Q1wk02	Dave	SERVICE	-38
APJC	24Q1wk02	Jim	SERVICE	7712
APJC	24Q1wk03	Jim	HARDWARE	26689
APJC	24Q1wk03	Jim	SOFTWARE	18758
APJC	24Q1wk03	Dave	HARDWARE	32694
APJC	24Q1wk03	Dave	SERVICE	-38
APJC	24Q1wk03	Dave	SOFTWARE	669
APJC	24Q1wk03	Jim	SERVICE	-443207
APJC	24Q1wk04	Jim	HARDWARE	26689
APJC	24Q1wk04	Jim	SOFTWARE	18758
APJC	24Q1wk04	Jim	SERVICE	-443207
APJC	24Q1wk04	Dave	SERVICE	-38
APJC	24Q1wk04	Dave	HARDWARE	32694
APJC	24Q1wk04	Dave	SOFTWARE	669
APJC	24Q1wk05	Dave	SOFTWARE	2520
APJC	24Q1wk05	Jim	HARDWARE	26689
APJC	24Q1wk05	Jim	SOFTWARE	18758
APJC	24Q1wk05	Dave	SERVICE	-38
APJC	24Q1wk05	Jim	SERVICE	-443207
APJC	24Q1wk05	Dave	HARDWARE	51142

[Jihwan_Kim · Accepted solution]

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.

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]
            )
    )

[unusdusrname]

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

[unusdusrname]

Brilliant. Thanks so much 😁 👍