Multiplying measure results from selected month to all months from a different column

[Anonymous]

Hey everyone,

I’ve got a simple concept I’m struggling with calculating.

I want to display the $/UOM at a point in time the user selects through the calendar year and apply that rate to the forecast qty

Here’s what I have:

• A table used as a slicer for the user to display which month’s forecast they want to use (Jan forecast, Feb forecast, etc.)
• A table of total $ and $/UOM actuals by month
• A table of forecasted qty by both month and month version (i.e. Jan version has Jan-Dec forecast qty, Feb version has Feb-Dec forecast qty, etc)
• A generic calendar table – connected to both the actuals and forecast table

My end goal is to display a bar chart that shows actual $ and forecast $ by month based on which forecast version the user selects (i.e. If user selects Feb forecast, it will show Jan actual $ + Feb thru Dec forecast qty * Jan actual $/IEA to get forecasted $ for Feb thru Dec

Here’s what I’ve got:

• Total Actuals $ and $/UOM by Month – complete
• Calculated Actual $ - Measure that only displays the actuals based on the monthly forecast version (in the case below the forecast version selected was May, so actuals were displayed only for Jan – Apr) – complete
• Measure that grabs the $/UOM of the last month of actuals based on the monthly forecast version selected (since May was selected – April’s $/UOM of $35.20 is displayed) – complete
• Forecast Qty for future months based on forecast selected (since May was selected – May thru December qty is displayed – in this specific case there was no forecast volume for May which is why it’s blank) – complete

What I’m missing

• Measure that takes the Selected $/UOM (in this case of $35.20) and multiply that by each of the Calculated Forecast Qty values to get the Forecast $ (it calculates at the total level - $35.20 * 6,854 = $241,263.54, but I need it at the month level so I can chart it)

Thanks for your help

Image of Table

[MattAllington · Accepted solution]

Firstly, congratulations on laying out your measures in a table so you can see what’s is happening. This is essential for more complex DAX. You can see that your uom measure is only being returned for month 4 and the total. You need the number to be returned for all months. Stated differently, you need to remove the filter from what ever is causing the measure to only work for month 4. It is probably something like CALCULATE(your current code, all(calendar[month number]))

[Anonymous]

I knew it had to be something simple.  Now I can I enjoy the rest of my weekend.  Thanks