CrossJoin off two columns combination with one column

[farooqk]

I have this data:

Date	MasterCategory	SubCategory	Amount
8-August-2022	Food	Grocery	200
8-August-2022	Food	Grocery	31
8-August-2022	Food	Restaurant	40
8-August-2022	Shopping	Electronics	30
9-August-2022	Food	Grocery	10
10-August-2022	Shopping	Electronics	5
11-August-2022	Food	Restaurant	10

my goal is to generate:

Date	MasterCategory	SubCategory	Amount
8-August-2022	Food	Grocery	231
8-August-2022	Food	Restaurant	40
8-August-2022	Shopping	Electronics	30
9-August-2022	Food	Grocery	10
9-August-2022	Food	Restaurant	0
9-August-2022	Shopping	Electronics	0
10-August-2022	Food	Grocery	0
10-August-2022	Food	Restaurant	0
10-August-2022	Shopping	Electronics	5
11-August-2022	Food	Grocery	0
11-August-2022	Food	Restaurant	10
11-August-2022	Shopping	Electronics	0

Please do note that amount value in first row of above column is sum by master,sub and date.

I have got this partially worked out with below code but it is missing the mastercategory column, hopefully someone can help me:

/* START QUERY BUILDER */
DEFINE MEASURE
'myBank'[Sum_Measure] =
VAR _A =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'myBank'[SubCategory] ), VALUES ( 'myBank'[Date] ) ),
        "M1",
            CALCULATE (
                SUM ( 'myBank'[Amount] ),
                FILTER (
                    'myBank',
                    'myBank'[SubCategory] = EARLIER ( [SubCategory] )
                        && 'myBank'[Date] = EARLIER ( [Date] )
                )
            ) + 0
    )
VAR _B =
    SUMX ( _A, [M1] )
RETURN
    _B
EVALUATE
SUMMARIZECOLUMNS(
myBank[SubCategory],
myBank[Date],
"Sum Col", [Sum_Measure]
)
ORDER BY
myBank[Date]
/* END QUERY BUILDER */

[amitchandak · Accepted solution]

@farooqk , I think a simple measure with dimension should do if needed add a date table too .

Please find the file attached