Help with applying a cross-filter from a table filtered by 1 month to a line chart with 12 months.

[dapperscavenger]

I have built a dashboard to look at issues with inventory.  It has a line chart showing inventory of the last12 months (rolling, including this month) against the same period previous year.

The other three visuals - a bar chart, a matrix, and a table, are all filtered by the latest month i.e. what we have in warehouses right now.

When I filter using the normal filters everything works fine:

But when I filter by any of the other three visuals the line chart only shows the latest month:

 I mean, it makes perfect sense that powerbi behaves this way but how do I get it to NOT behave that way?

I would like for people to be able to click on a the other visuals and see the trends chart update. 

  I've been playing around with some formulas but so far nothing is working. Latest attempt was:

FilterUoM = IF(
HASONEVALUE( SKUData[SKU] ),

CALCULATE( [UoM], ALL ( Dates[Date] )),

[UoM] )

Thanks for the help

[lbendlin]

Click on the line chart, Format, Edit Interactions.

[dapperscavenger]

Using 'edit interactions' there seems to be only the option to turn the filtering off or on.

what I want to do is keep the filtering on, but have it filter only by e.g the SKU in the table, and NOT by the date.

[lbendlin]

then you need to create two measures ( one for each year) that remove the date filter context, and replace your existing values in the line chart with these measures.

[dapperscavenger]

Alright, yes, that helps!

But my formula is not perfect.  

This works really well when a SKU has volume in the most recent month in the report.  However, if a SKU has had no volume since, e.g., February 2019, the table shows the value from the most recent month that it can find.  e.g. 1879 cases in Feb 2019 instead of 0 cases in August 2020 (my latest date in my data)

Also my line chart goes up to February 2019 and no further.  It does not go up to August 2020 (the lztest date I have) and show a zero value after February 2019. 

Note: In the line chart I have a top 12 my earliest date in the filters, so I only see 12 months rolling.

This is the code I used to get the 'max month' from my values.  I can already see the problem that it is only taking the max month that it can find for that partiicular filter e.g. SKU.  But I'm not sure best way to fix it.

UoM TM = CALCULATE(

MyValueHere

, FILTER(ALL(Dates[Date]) , Dates[Date] = MAX(Inventory[InventoryDate]))
)

Hmm... How can I get this to say zero if it can't find the actual 'last month' in the report?  And/or to show 0 against any SKU that doesn't have an entry that month?

 Here is a screenshot:

Is there somewhere I can upload my desktop pbi file to, so that you could take a look?

[dapperscavenger]

Ooh , wait. 

I have a dates table.  

maybe if I filter my dates table by removing all future dates in the dates table, maybe by joining on the inventory table, then I can write the formula to take the max of the dates table, not the max of the inventory date. 

then pull the dates into the line chart axis instead of the inventory date.

hmmm... what do you think?

it might actually just end up having exactly the same problem.  Not sure 

[lbendlin · Accepted solution]

Enable "show items with no data"  and choose your date column wisely.

[dapperscavenger]

Alright, took a fair bit of jiggling but looks like it works just fine now, thank you!