Measure using current inventory measure

Anonymous , not very clear, But try the calculation like

CALCULATE(
SUM('Table'[Quantity]),
FILTER(
ALLSELECTED('Date Table'),
'Date Table'[Date] <= max('Date Table'[Date])
)
)

Hi Anonymous ,

You can try the following 5 measures:

Total Jumbo = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<= MAX(Dim_Date[Date])),'Table'[Storage Type] = "Jumbo")+0

Total Pallet = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<= MAX(Dim_Date[Date])),'Table'[Storage Type] = "Pallet")+0

Total = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<=MAX(Dim_Date[Date])))

% Jumbo = [Total Jumbo]/[Total]

% Pallet = [Total Pallet]/[Total]

For more details, please refer to the sample pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfHgP-6ymjhOt-eElIg0J5AB16pWEqFcEeYAe0c5ZMXvqw?e=BPsHbW

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

Best Regards,

Dedmon Dai

v-deddai1-msft 

Hi Dedmon, that only takes into account the inventory levels on the last day of the month, not daily inventory levels. I have continued the thread in this post: https://community.powerbi.com/t5/Desktop/Measure-using-current-inventory-measure-response/m-p/1574407#M640453 as I'm not able to reply with anything besides a text or link.

v-deddai1-msft 

Hi Dedmond,

that does work, but is there a way to do it in a measure? Because I also have other attributes, such as business classification and valuation class linked to each posting (was not included in sample data) and they're only in the posting table. I have slicers for those attributes and would like it to work on the jumbo/pallet calculation also, i.e. if I choose food as business classification on the slicer, I only want the jumbo/pallet classification done for the materials associated with food as business classification. Currently, when a new table is created, those slicers from the posting table won't work.

Hi Anonymous ,

You can add those attributes in the summary table. I don't suggest you use measure in this case. You need to create temporary table like above in every measure. This can make your measure look bloated and affect performance. Because every measure needs to compute this temporary table once.

Best Regards,

Dedmon Dai

Hi v-deddai1-msft,

What do you mean by adding the attributes from the summary table? I currently have a slicer for storage location with 118 distinct values that I would like to include. Does this mean that for the inventory_on_date table, I would have to cross join storage location, plant code (11 values), and date (over 2 years of dates - 735 values), resulting in 954,030 rows?