Cumulative sum measure filtered by conditions on two diferente tables

[Anonymous]

Hi guys,

Need a bit of a help here with a dax measure.

For context, here's a printscreen of my current data model.

If I want to calculate the stock value in a given period I can use the following measure:

Stock =

CALCULATE(
    SUM(MaterialMovimento[Quantidade]);
    FILTER(ALL(Datas[Data]); Datas[Data] <= MAX(Datas[Data]))
)

But the problem starts when I want to calculate the stock value in given period of time, but just for materials which are slow movers. In other words, materials that don't have any sale in the past 3 months for any given date.

I've tried to create a measure to plot the stock value of slow movers over time but it's just not working. Here's the dax measure I'm unsuccesfully working on:

SlowMoversStock = 

VAR SlowMovers = CALCULATETABLE(Material; FILTER(ALL(ClienteFaturacao); ClienteFaturacao[DataCriacao] <= DATEADD(Datas[Data]; -3; MONTH)))

Return

CALCULATE(
    SUM(MaterialMovimento[Quantidade]);
    FILTER(ALL(Datas[Data]); Datas[Data] <= MAX(Datas[Data]));
    INTERSECT(Material; SlowMovers)
)

Unfortunatelly, with this measure I'm not able to get any value. All the graphics shown below were ploted with yaxis = SlowMoversStock and xaxis = Datas[Data]

I think the problem it's somehow related with the date filtering (probably because of the relationships on the data model) I'm trying to perform when creating the calculated table to get the materials which are slow movers. You can see that I filter with a fixed date, the graphic shows values, as you can see in the code and image below.

SlowMoversStock = 

VAR SlowMovers = CALCULATETABLE(Material; FILTER(ALL(ClienteFaturacao); ClienteFaturacao[DataCriacao] <= DATE(2019; 7; 7)))

Return

CALCULATE(
    SUM(MaterialMovimento[Quantidade]);
    FILTER(ALL(Datas[Data]); Datas[Data] <= MAX(Datas[Data]));
    INTERSECT(Material; SlowMovers)
)

Any help would be appreciated. Thanks in advance!

[kentyler]

Any chance you could send a small power bi file with some sample data?

[Anonymous]

Of course! Here you have it: https://drive.google.com/file/d/1-Pzaar2ZFnpurDpOdvIKhfIE3O-Q-87w/view?usp=sharinghttps://drive.goog...

Any doubt you have please feel free to contact me.

Thanks!