Project Last Month's Calculated Cost

[gabriel_264]

Hello, everyone.

I need to come up with the unit cost of my company (value/qty) based on the last three months of data and then repeat the last real value for the next X months so I can come up with the final cost for the rest of the forecasted year since only volume is forecasted.

Problem is that the information is affected by Business Unit, so I need to have the ability to:

- Calculate the last month's Unit Cost per BU and Global.

- Project that for the rest of the year or following X months.

- Allow users to slice per BU or see the Global value.

Involved Tables:

- Dim_Date
- Stock

- Dim_BU

  

[Floriankx]

Hello,

I assume the Unit columns are related as well as the date columns.

If you add a measure Total Sum:=SUMX(Stock,Stock[Value_Stock_Total]/Stock[Cant_Stock_Total] you get the global some over all Business Units. Then you can slice or group by Period and BU however you like.

[gabriel_264]

The Units are a dimension. Basically lists the business units and their codes (US, ARG, COL, etc.) so it has no date. The Stock concentrates the date and unit affected.

What you say actually works and I've tried doing it with CALCULATE as well, but the problem remains: how can I project that same value for future months so I can calculate a forecast? Coming up with past values is not a problem.

Thanks!

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490