Count days with filters

[Sperling]

I have a fact table with items and a dummy for whether the item is deadstock or not.

This is stamped on the items each day, so we can see the history of the items we have.

The fact table is connected to our item dimension, the date dimension and the deadstock time dependent dimension.

I want to create a measure that calculates how long the item has been deadstock IF it currently is deadstock.

I've approached this in a few different ways without luck.

Below is an image of a random item that is currently marked as deadstock:

The value I'm interested in here is 9.

I figured it was as simple as:

1. finding the max date where Deadstock = No

2. finding the max date where Deadstock = Yes

3. making sure the measure only calculates for cases where Deadstock on today() - 1 = Yes (important)

4. and then finally counting the rows

I can't just count rows where Deadstock = Yes, since I don't want to include old periods where it was Deadstock, only the current period.

It has proved more difficult than anticipated, so I'm open to suggestions.

[isjoycewang]

Hi @Sperling ,

Attached the demo file. Does it make sense to you?

Count = 
VAR _item = SELECTEDVALUE(DimItem[DW_SK_Item])
VAR _min_date = CALCULATE(MAX(DimDate[Date]), DimDeadstock[Deadstock] = "No", REMOVEFILTERS(DimDate[Date]))

RETURN
CALCULATE( COUNT(FactInventory[DW_SK_Date]), 
FILTER(ALL(FactInventory), FactInventory[DW_SK_Item] = _item && RELATED(DimDate[Date]) > _min_date))

[Sperling]

Hi @isjoycewang 

Appreciate the response! I attempted to use it in my actual model and realized I made a mistake in the sample dataset.

PBI applied both cross-filter direction to the date dimension, which it wasn't supposed to. After I changed it to single cross-filter direction, the measure was blank.

I've attached the file again here, but in your model you can also just change the cross-filter setting.

https://drive.google.com/file/d/1UO5JbDorQBR-LbaoJD333yLvC02eKF-5/view?usp=drive_link

[lbendlin · Accepted solution]

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Sperling]

I've made a sample here:

https://drive.google.com/file/d/1fDsLFh9XHxx2HZ6ZfjjV6HbYOA8J28t6/view?usp=sharing

Table on the left is the type of table I'm interested in making, where the day count should be a measure next to inventory.

Table in the middle is an example of an item, where the deadstock day count should be 13 (days with Yes in Deadstock column).

Table on the right is another example, where we have two periods with Deadstock = Yes. The count should only count if the current status is Deadstock = Yes, and should only count the current period, which means from the 13th to the 23rd = 11 days.