DAX: Calculate, Filter problem, dead stock calculation

[iNekros]

Hey guys,

Im still new to DAX. Im trying to calculate measure which is called Dead stock. The measure definition is following, its Inventory status for items, which havent been sold for 8 previous weeks. So I have calculated measure for sum of last 8 week sales.

[sumSalesLast8weeks]=CALCULATE([TotalSales],
DATESINPERIOD(DateByDay[DateKey],
LASTDATE(DateByDay[DateKey]),-56, Day
)
)

and then Im trying to use calculate with filter to calculate only sum of inventory where last 8 week sales equals 0
[DeadInventory]=CALCULATE(sum(FactTable[Inventory]),FILTER(FactTable,[sumSalesLast8weeks]=0))

but the problem is following that filter only values where are current week sales are zero, not the last 8 weeks sales are zero.

Do you have any advices, how to solve this problem?

Best regards

iNekros

[mondinelli]

mmm.

In my opinion is wrong by desing:

I'd preferred a calculated COLUMN on the Fact table that calculate the [sumSalesLast8weeks] and use a IF statement that puts a value like ACTIVE if >0 or DEAD if = 0.

isn't it better? Then u can easily filter by that column.

[nikil · Accepted solution]

Yep I agree, calculated column approach is a better design. And you can use EARLIER function for the last 8 weeks logic. Here is a link to EARLIER function (https://msdn.microsoft.com/en-us/library/ee634551.aspx)

Hope this helps.

Check out the Chicagoland Power BI User Group 

[iNekros]

thank you, its really helpful ( I knew, that I misunderstood DAX concept a little now are things much clearer too me).