Cost change for products in different month.

[ck1995]

I have a fact table that combines monthly files of cost of material consumed for product untill completion. some products get cancelled so they dont show up in next months file.

I want to only check the sum of cost of material consumed only for a particular month's product and I have to make it dynamic based on slicer selection.

What I have tried is not working in dax, as I am not sure how to create a virtual table and then lookup values from previous month (which has to be filtered from the common fact file) and then sum. I have created two seperate tables, 

table 1 has distinct values of latest months product

table 2 has cost of values of previous month 

then i lookup the values in table 1 and sum it. but that cannot be a solution. Please support.

Change in NTP basevalue = var defaultmonth = VALUE(max(datafile[File date].[Year]) & FORMAT(eomonth(TODAY(),-2), "mm")) var _min = if(isfiltered(datafile[Current Month]),CALCULATE(Min((datafile[Current Month])), ALLSELECTED(datafile[Current Month])),defaultmonth) var _max = CALCULATE(MAX((datafileI[Current Month])), ALLSELECTED(datafile[Current Month])) VAR BASETABLE = SUMMARIZECOLUMNS(datafile[Serial number],"distinct",cALCULATE(DISTINCT(datafile[serial number]),datafile[Current Month] IN {_max})) VAR __BASELINE_VALUE =sumx(SUMMARIZE(datafile,datafile[serial number],"vbelncolm",BASETABLE),calculate(sum(datafile[AP]),[vbelncolm],datafile[Current Month] IN {_min})) VAR __MEASURE_VALUE = calculate(SUM(datafile[AP]),'Datafile'[current month] IN {_max}) RETURN __MEASURE_VALUE - __BASELINE_VALUE

Serial number	File date	Current month	Actual cost(AP)
A11	01 January 2022	202201	110
A22	01 January 2022	202201	-190
A33	01 January 2022	202201	310
A44	01 January 2022	202201	410
A55	01 January 2022	202201	-510
A11	01 February 2022	202202	100
A33	01 February 2022	202202	300
A44	01 February 2022	202202	400
A55	01 February 2022	202202	-480
A11	01 March 2022	202203	90
A22	01 March 2022	202203	190
A44	01 March 2022	202203	390
A55	01 March 2022	202203	-490

[v-rzhou-msft · Accepted solution]

Hi @ck1995 ,

I suggest you to create a related Dimdate table to calculate the change in cost between current month and previous month.

Datafile table is the same like yours. DimDate table is based on code as below.

DimDate = 
ADDCOLUMNS (
    CALENDAR(DATE(2022,01,01),DATE(2022,12,31)),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Relationship:

Measure:

Measure = 
VAR _CurrentMonth_Cost = CALCULATE(SUM(datafile[Actual cost(AP)]))
VAR _PreviouMonth_Cost = CALCULATE(SUM(datafile[Actual cost(AP)]),PREVIOUSMONTH(DimDate[Date]))
RETURN
_CurrentMonth_Cost - _PreviouMonth_Cost

Result is as below.

Best Regards,
Rico Zhou

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