Difference for running total

[svishwanathan]

Hello

I have a dataset that needs to be normalized. THere is a column for sales which is a running total. I want to derieve individual sales amount on each row so I can do calculations like ytd, qtd etc

Job        YYYYMM      Sales (Running Total)                    Sales (desired column)

A              201803           450                                                 200

B               201802            300                                                 200

A              201801            250                                                   50

A               201709            200                                                   200

B              201801              100                                                  100    

Regards

Swati

[Zubair_Muhammad · Accepted solution]

@svishwanathan

Give this a shot please

Sales Column =
VAR PreviousYYMM =
    CALCULATE (
        MAX ( TableName[YYYYMM] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Job] ),
            TableName[YYYYMM] < EARLIER ( TableName[YYYYMM] )
        )
    )
VAR PreviousCumulative =
    CALCULATE (
        SUM ( TableName[Sales] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Job] ),
            TableName[YYYYMM] = PreviousYYMM
        )
    )
RETURN
    TableName[Sales] - PreviousCumulative

[svishwanathan]

Thanks all work well