Plot multiple moving averages on one line & column chart

[tj1947]

I want to plot the moving average of concrete strength for multiple concrete mixes over time... on one chart.  Is this possible?

Data table looks somethign liks this:

Mix	Break_Strength	Break_Date
1516	4300	01/01/2021
1516	3200	01/02/2021
1516	3700	01/05/2021
1516	4500	01/06/2021
1516	4000	01/07/2021
1517	4500	01/01/2021
1517	4700	01/02/2021
1517	5200	01/05/2021
1517	5000	01/06/2021
1517	5100	01/07/2021

I've been able to create a chart that looks like this (different dataset but same principle)

PROBLEM with the chart above:  The Moving Average line is for both Mix 1516 & Mix 1517.    What I want is 1 moving average line for Mix 1516 and a separate moving average line for Mix 1517.   The Mix Numbers can vary over time.  Next week, one Mix Number could be discontinued and another one added so I can't create separate measures for each mix using a Mix Number filter because the Mix Numbers can change over time.  

Is there a way to accomplish this?

[v-luwang-msft]

Hi @tj1947 ，

I did some research and I'm sorry I didn't find any way to display it dynamically. Of course, if you find a better way, you can share it and mark it to help more people with the same problem.

Best Regards

Lucien

[v-luwang-msft]

HI  @tj1947 ,

Try to create measure like below:

1516aver = CALCULATE(AVERAGE('Table'[Break_Strength]),FILTER(ALL('Table'),'Table'[Mix]=1516&&MAX('Table'[Break_Date])>='Table'[Break_Date]))

1517aver = CALCULATE(AVERAGE('Table'[Break_Strength]),FILTER(ALL('Table'),'Table'[Mix]=1517&&MAX('Table'[Break_Date])>='Table'[Break_Date]))

Then create visual :

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[tj1947]

Yes, that works.  But that 'hard codes' the mix numbers.  My issue is that the mix numbers can change over time.  I need a solution that works dynamically and does not require me to edit the visual every time a mix number changes.  Not sure it's possible.