Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iNekros
New Member

DAX: Calculate, Filter problem, dead stock calculation

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

1 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 

View solution in original post

3 REPLIES 3
mondinelli
Advocate I
Advocate I

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.

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 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.