calculating measure based on multiple fact tables and dim table (dims not related)

[olt]

Hi,

I have a data model that has multiple fact tables (+-10, all different granularity), and 2 dim tables, Product and Date. The dim tables are linked to the fact tables. 

I need to calculate measure based on multiple fact tables and on the granularity Product-year-month. What i need to calculate is number of month before i run into 0 or negative inventory. The visual i will use the measure in is matrix, showing values on product level, date aspect is not in the visual. So the calcuations need to be done per Year month for every product but visualisation in on product.

Is it possible to do? Using variables maybe?

I have tried to define table in a measure using the logic but it is apparently incorrect as within the visual it returns the same value for every product.

Could someone help me understand what the problem is? and/ or is it is possible to calculate what i need as a measure or i need to create calculated table fact/ dim ?

Thank you, Ol

=================================

periodscovered =

// create table with product-year-month level of detail

var _sumdim = SUMMARIZECOLUMNS('Date'[Year month], 'Date'[Current month], 'Product'[Product abbreviated])

// filter out the past

var _sumdimfromcurrent = (FILTER(_sumdim, 'Date'[Year month] >= FORMAT(TODAY(), "yyyy-mm")))

// add fact columns

var _ForecastvsExpected&Onhand = ADDCOLUMNS(_sumdimfromcurrent,  "forecast", [Sales Forecast], "remaining", [RemainingTotalMtm], "on hand", [Inventory usable on hand BoM])

//create logic for dif: cummulative inv plus cummulative remaining minus cummilative forecast

var _cummulativeremaining = CALCULATE([RemainingTotalMtm]

                                , FILTER(ALLSELECTED('Date'), 'Date'[Year month]<=MAX('Date'[Year month])))

var _cummulativeinvonhand = CALCULATE([Inventory usable on hand BoM]

                                , FILTER(ALLSELECTED('Date'), 'Date'[Year month]<=MAX('Date'[Year month])))

var _cummulativeforecast = CALCULATE([Sales Forecast]

                                , FILTER(ALLSELECTED('Date'), 'Date'[Year month]<=MAX('Date'[Year month])))

var _inventorypositionpermonth = _cummulativeinvonhand+_cummulativeremaining-_cummulativeforecast

------------------------------------------------------------------------------------------------------------------

//final table

var _finaltable = ADDCOLUMNS(_ForecastvsExpected&Onhand, "finalinvpermonth",  _cummulativeremaining+_cummulativeinvonhand-_cummulativeforecast)

//keep only the negative or 0 inventory rows, the minimun year month will be the period when runnign into shortage

var _finalfiltered = FILTER(_finaltable, [finalinvpermonth]<=0)

return MINX(_finalfiltered, 'Date'[Current month])

[olt]

THank you for this, what do you mean by adding necessary filters exactly? via functions?

[jaweher899]

Yes, it is possible to calculate what you need as a measure. Using variables can be helpful to simplify the measure and improve performance.

Regarding the issue of returning the same value for every product within the visual, it is likely that the measure is not properly filtered by the Product dimension. You can try adding the necessary filters to the measure to ensure that it is being evaluated at the correct granularity.

However, it is also important to note that using a calculated table (either fact or dim) may be a more efficient way to approach this calculation, especially if your data model has many fact tables. By creating a calculated table with the necessary granularity, you can simplify your measures and improve query performance.