Calculated column for Daily values from Cumulative values

[sayali_deshmukh]

Hi,
Need a calculated column for daily values from cumulative values type and category wise. Table is as per below -

Type	Category	Date	Cumulative	Daily
Domestic	A	21-04-2020	1
Domestic	A	10-05-2020	5
Domestic	A	11-05-2020	9
Domestic	B	21-04-2020	15
Domestic	B	22-04-2020	16
Domestic	B	29-04-2020	32
Domestic	B	11-05-2020	32
Domestic	C	21-04-2020	1
Domestic	C	22-04-2020	1
Domestic	C	23-04-2020	1
Domestic	C	24-04-2020	9
Export	E	21-04-2020	36
Export	E	22-04-2020	36
Export	E	25-04-2020	51
Export	E	26-04-2020	53
Export	F	27-04-2020	53
Export	F	28-04-2020	54
Export	F	29-04-2020	58
Export	F	30-04-2020	61
Export	F	01-05-2020	67
Export	F	02-05-2020	71
Export	F	03-05-2020	78
Export	F	04-05-2020	78
Export	F	05-05-2020	80
Export	F	06-05-2020	80
Export	F	07-05-2020	83

[v-yuta-msft]

@sayali_deshmukh ,

Create two calculate columns using dax as below:

Rank = RANKX(FILTER('Table', 'Table'[Type] = EARLIER('Table'[Type]) && 'Table'[Category] = EARLIER('Table'[Category])), 'Table'[Index], , ASC, Dense)

Column = 
VAR Current_Rank = 'Table'[Rank]
VAR Current_CumulativeDaily = 'Table'[CumulativeDaily]
RETURN
    IF (
        Current_Rank = 1,
        Current_CumulativeDaily,
        Current_CumulativeDaily
            - CALCULATE (
                MAX ( 'Table'[CumulativeDaily] ),
                FILTER (
                    'Table',
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Type] = EARLIER ( 'Table'[Type] )
                        && 'Table'[Rank] = Current_Rank - 1
                )
            )
    )

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @sayali_deshmukh ,

Try this code:

Daily = CALCULATE(SUM('Table'[Cumulative]); FILTER('Table'; 'Table'[Type] = EARLIER('Table'[Type]) && 'Table'[Category] = EARLIER('Table'[Category]) && 'Table'[Date] <= EARLIER('Table'[Date])))