The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Any chance you could send a small power bi file with some sample data?
Help when you know. Ask when you don't!
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!
User | Count |
---|---|
12 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |