Forum Discussion
rajibmahmud
8 years agoHelper III
Cost calculation from Bottomup
Hi Team, I am trying to calculate a cost using bottom up method at monthly level. Where cost would be Vol sold in Month 1, multiplied by Cost in month 1 at Product Level. The total year cost ...
rajibmahmud
8 years agoHelper III
It doesnt work. As in the tables Month and Year is not unique due to dataset nature.
Floriankx
8 years agoSolution Sage
Could have been tables aggregated to Year, Month and SKU.
In this case it probably would be best to create a DateTable (dDate) with distinct Year and Month Values.
Additionally I would create a distinct table for SKU (dSKU).
The tables shouldn't be related.
The I added in dDate the following Measure:
Total Cost:=SUMX(dSKU; var Actual_SKU=dSKU[SKU] var Date_Value=SUMX(dDate; var Actual_Year=dDate[Year] var Actual_Month=dDate[Month Number] var Cost_per_month=CALCULATE(SUM(fCost[SCC.Ton]);FILTER(fCost;fCost[Year]=Actual_Year&&fCost[Month]=Actual_Month&&fCost[SKU]=Actual_SKU)) var Value_per_month=CALCULATE(SUM(fValue[Value]);FILTER(fValue;fValue[Year]=Actual_Year&&fValue[Month]=Actual_Month&&fValue[SKU]=Actual_SKU)) Return Cost_per_month*Value_per_month) Return Date_Value)
Please check it out.
There would also be the possibilitie of creating dates out of Month and Year and relate them to your date table.