Incorrect Totals: - Matrix

[EnrichedUser]

Hi, 

I have an issue with a measure(s) that I have exhuasted all other resources to solve. 

I am trying to solve for "Monthly Inflation" which is defined as:
( [Last Unit Cost] - [Base Unit Cost] ) * [Average Monthly Usage]

I have included all relevent information below to create a sample model of the data as I cannot share the file. 

The receipt data contains only transactions that occur, but I am attempting to calaculate a value when there is no data. My usual sumx, addcolumns, summarize method is not working as I cannot seem to create a virtual table where all 12 months are included. 

Any help would be greatly apprecaited!

Table1: Receipts

Order	Line	Eff Date	Unit Cost	Extended Cost	Quantity	Site_Item_Key
1234	1	44998	0.097	9.7	100	SFA250
1235	1	45016	0.097	9.7	100	SFA250
1236	2	45103	0.09327	9.327	100	SFA250
1237	1	45019	92.71	927.1	10	SFA479
1238	1	45057	92.71	927.1	10	SFA479
1239	1	45099	26.38	8045.9	305	SFA479
1240	1	44938	54.26	325.56	6	SFA479

Table2: Base Cost

Site_Item_Key	Base Cost
SFA250	0.1011
SFA479	54.26

Table3: Demand

N360 Days Demand	Site_Item_Key
300	SFA250
309	SFA479

Table4: Date

Measures:

AMU = 
CALCULATE(
    SUM('Demand'[N360 Days Demand]) / 12,
    CROSSFILTER(Receipts[Site_Item_Key], 'Demand'[Site_Item_Key], Both),
    ALL('DateTable')
)

Avg Unit Cost (R) = 
DIVIDE( [Receipt COGS], [Receipt QTY], BLANK() )

Base Cost (R) = 
CALCULATE(
    SELECTEDVALUE( 'Base Cost'[Base Cost] ),
    CROSSFILTER( 'Base Cost'[Site_Item_Key], Receipts[Site_Item_Key], Both )
)
---------------------------------------------------------------------------------------------------------------------------------------
Last Unit Cost (R) = 
VAR LastMonthWithReceipts =
    CALCULATE(
        MAX('DateTable'[Month Year No]),
        FILTER( ALLSELECTED( 'Receipts' ),
        'Receipts'[Eff Date] <= MAX('DateTable'[Date])
        )
    )
VAR ReceiptCost = 
    CALCULATE(
        [Avg Unit Cost (R)],
        FILTER( ALLSELECTED( 'DateTable' ),
        'DateTable'[Month Year No] = LastMonthWithReceipts
    )
    )
RETURN ReceiptCost
---------------------------------------------------------------------------------------------------------------------------------------
Monthly Inflation (R) = 
VAR BaseUnitCost = CALCULATE( [Base Cost (R)], ALL( 'DateTable' ) )
VAR Result = ( [Last Unit Cost (R)] - BaseUnitCost ) * [AMU]
RETURN Result
---------------------------------------------------------------------------------------------------------------------------------------
Receipt COGS = 
SUM( Receipts[Extended Cost] )
---------------------------------------------------------------------------------------------------------------------------------------
Receipt QTY = 
SUM( Receipts[Quantity] )
---------------------------------------------------------------------------------------------------------------------------------------

DateTable = 
ADDCOLUMNS (
   CALENDAR ( DATE(2023,1,1), DATE(2023,12,31) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month No", MONTH ( [Date] ),
    "Quarter", "Q" & CEILING ( MONTH ( [Date] ) / 3, 1 ),
    "Week No", WEEKNUM ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday No", WEEKDAY ( [Date], 2 ) + 1,
    "Month Year", FORMAT ( [Date], "MMM yyyy" ),
    "Month Year No", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Week Year", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
    "Month Sort", YEAR ( [Date] ) * 10000 + MONTH ( [Date] ),
    "Week Sort", YEAR ( [Date] ) * 10000 + WEEKNUM ( [Date] )
)

Model:
Expected Output:

Site_Item_Key	Month Year	AMU	Base Cost (R)	Avg Unit Cost (R)	Last Unit Cost (R)	Monthly Inflation (R)	Comments
SFA250	Jan-23	25	0.1011			$0.00	Zero Because Last Unit Cost is assummed to be equal to Base Cost
SFA250	Feb-23	25	0.1011			$0.00	Zero Because Last Unit Cost is assummed to be equal to Base Cost
SFA250	Mar-23	25	0.1011	0.097	0.097	($0.10)	( Last Unit Cost (0.097) - Base Cost (0.1011) ) * AMU (25)
SFA250	Apr-23	25	0.1011		0.097	($0.10)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	May-23	25	0.1011		0.097	($0.10)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Jun-23	25	0.1011	0.09327	0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Jul-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Aug-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Sep-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Oct-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Nov-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
SFA250	Dec-23	25	0.1011		0.093	($0.20)	( Last Unit Cost - Base Cost  ) * AMU
						($1.67)	Sum of Monthly Inflation Jan to Dec
SFA479	Jan-23	25.75	54.26	54.26	54.260	$0.00	( Last Unit Cost (54.260) - Base Cost (54.26) ) * AMU (25.75)
SFA479	Feb-23	25.75			54.260	$1,397.20	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Mar-23	25.75				$0.00	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Apr-23	25.75	54.26	92.71	92.710	$990.09	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	May-23	25.75	54.26	92.71	92.710	$990.09	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Jun-23	25.75	54.26	26.38	26.380	($717.91)	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Jul-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Aug-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Sep-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Oct-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Nov-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
SFA479	Dec-23	25.75			26.380	$679.29	( Last Unit Cost  - Base Cost  ) * AMU
						$6,735.17	Sum of Monthly Inflation Jan to Dec
						$6,733.50	Grand Total

[EnrichedUser]

I have made significant progress, but am not seeing the totals sum up correctly for the year month now.

Monthly Inflation (TEST2) = 
VAR VirtualTable = 
ADDCOLUMNS(
    SUMMARIZE(
        GENERATE(
            VALUES(Receipts[Site_Item_Key]),
            VALUES('Date'[Year Month])
        ),
        Receipts[Site_Item_Key],
        'Date'[Year Month]
    ),
    "@Monthly Inflation", [Monthly Inflation (R)]
)
VAR VirtualTable2 = 
ADDCOLUMNS(
    SUMMARIZE(
        GENERATE(
            VALUES(Receipts[Site_Item_Key]),
            VALUES('Date'[Year Month])
        ),
        Receipts[Site_Item_Key],
        'Date'[Year Month]
    ),
    "@Monthly Inflation", [Monthly Inflation (R)]
)
RETURN
SWITCH(
    TRUE(),
    ISINSCOPE( Receipts[Site_Item_Key] ) && ISINSCOPE( 'Date'[Year Month] ), [Monthly Inflation (R)],
    ISINSCOPE( Receipts[Site_Item_Key] ), SUMX( VirtualTable, [@Monthly Inflation] ) ,
    ISINSCOPE( 'Date'[Year Month] ), SUMX( VirtualTable2, [@Monthly Inflation] ), -- HELP
    SUMX( VirtualTable, [@Monthly Inflation] )
)

The totals for each year month are wrong.

[EnrichedUser]

Thanks for the attempt, but was not sucessful. 

Here is my latest go:

Monthly Inflation (TEST) = 
VAR VirtualTable = 
ADDCOLUMNS(
    SUMMARIZE(
        GENERATE(
            VALUES(Receipts[Site_Item_Key]),
            VALUES('Date'[Year Month])
        ),
        Receipts[Site_Item_Key],
        'Date'[Year Month]
    ),
    "@Monthly Inflation", [Monthly Inflation (R)]
)
RETURN
IF(
    ISINSCOPE( Receipts[Site_Item_Key] ) && ISINSCOPE( 'Date'[Year Month] ), 
    [Monthly Inflation (R)],
    SUMX( VirtualTable, [@Monthly Inflation] )
)

[amitchandak]

@EnrichedUser , Try a measure like this

Sumx(summarize(Table, Table[Order], Table[Line], Table[Eff Date], "_1",( [Last Unit Cost] - [Base Unit Cost] ) * [Average Monthly Usage]), [_1])

if needed add additional column from visual