Events in Progress Problem

[Jah8900]

I have some items in Stock. They arrive, some reworks are done, and they are released. This is what the table looks like:

I also have a Calendar table, created using MIN and MAX of my data table. It is related to my data table through an active relationship with Date_Received and an inactive relationship with Date_Released.

I'm trying to count the active stock per week. Item (13971004) was active for seven weeks (weeks 23 to 29). But my "Events in Progress" measure is returning an incorrect output. It shows, as an example, that (13971004) was active in weeks 23 and 31. That's wrong.

This is the actual "active" count for the weeks, but my measure will not do it:

For reference, this the measure:

Test_activeBS =

VAR MinDate = MIN(Calendar_active[Date])

VAR MaxDate = MAX(Calendar_active[Date])

VAR _result =

    CALCULATE(

        DISTINCTCOUNT(Table_query[Item_Number]),

        Table_query[Date_Received] <= MaxDate,

        Table_query[Date_Released] > MinDate

        ||

        ISBLANK(Table_query[Date_Released]),

        REMOVEFILTERS(Calendar_active)

    )

RETURN

    _result
What am I doing wrong?

Update 26/09/2025:
Link to sample data:
SampleData 

Link to sample result 1:
SampleResult_1 

Link to sample result 2:
SampleResult_2 

[Jah8900 · Accepted solution]

Thank you all for the responses. Appreciate all the help 🙂
I've figured out the logic. So, the right way to calculate events in progress is to set min and max dates (to count the number of days an item is open between its arrival and departure). In my case, I didn't care for the arrival date, but only the max date. I created a calculated column in my calendar table "Week End Date". For an item to be active/ open, it had to:
- have arrived on or before the max date (i.e. Week End Date)
AND
- have been released after the max date OR have a blank release date.

So, this is the DAX:

ActiveItems =
VAR _maxDate = MAX( 'Calendar_active'[Week End Date] )

-- get all ItemKeys but remove only the Calendar filter so we still respect other slicers/filters (ItemKey is a combination of Item_Number and Date_Received)

VAR _Items =
CALCULATETABLE(
VALUES( 'Table_query'[ItemKey] ),
REMOVEFILTERS( 'Calendar_active' )
)

RETURN
SUMX(
_Items,
VAR _minEff =
CALCULATE(
MIN( 'Table_query'[Date_Received] ),
REMOVEFILTERS( 'Calendar_active' )
)
VAR _maxRel =
CALCULATE(
MAX( 'Table_query'[Date_Released] ),
REMOVEFILTERS( 'Calendar_active' )
)
RETURN
IF(
_minEff <= _maxDate
&& ( ISBLANK( _maxRel ) || _maxRel > _maxDate ),
1,
0
)
)

//

The reason I'm using ItemKey is:
- An item can come into stock for more than one rework. Each rework is entered as a separate row. But an item is not considered fully released until ALL its reworks are released. 
And
- An item can come into stock, have reworks done on it, and be released.
- The same item can enter stock again a week or so later.

By using a combination of Item_Number and Date_Received, I group all the reworks together so they are considered one item, and if the item enters stock again after being released, then it is counted again (since it will have a different Date_Received).

Hope that makes it clear 🙂

[v-achippa]

Hi @Jah8900,

Thank you for the response and confirming that this DAX is working for you. I request you to please mark this post as Accept as Solution so that other community members who has similar issue will find it more easily.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Jah8900,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Hoangechip910 for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Hoangechip910]

hi

I think You are doing summarize by Weeks, so the date column will breakout your data, need remove it.

[Jah8900]

It doesn't work, even without the date column. I need to use only the week column in fact, and that too, on a column chart. But the numbers are not correct.

[v-achippa]

Hi @Jah8900,

The issue here is likely because the current measure is using boolean expressions directly inside CALCULATE, which do not evaluate row-by-row. Please use the below following measure:

Active_Items_Per_Week =
VAR StartDate = MIN ( 'Calendar_active'[Date] ) 
VAR EndDate = MAX ( 'Calendar_active'[Date] )
RETURN
CALCULATE (DISTINCTCOUNT ( Table_query[Item_Number] ),
        FILTER (
         ALL ( Table_query ), 
         Table_query[Date_Received] <= EndDate
          && (
          ISBLANK ( Table_query[Date_Released] )
          || Table_query[Date_Released] >= StartDate
           )
      )
)

This make sure that an item is only counted as active between its received and released dates

Thanks and regards,

Anjan Kumar Chippa

[Jah8900]

Thank you for the reply. No, I'm not using any BOOLEAN expression in my DAX. But I still tried your measure and this is the result:

Now it sort of just stacks the active items. It does not consider their release. 
For reference, W36 should be 8.

[v-achippa]

Hi @Jah8900,

The issue here is because of the active relationship between your Calendar and Date_Received, it is forcing the measure to only look at rows received in that week. Please use this below measure:

Active_Items_Per_Week =
VAR StartDate = MIN ( 'Calendar_active'[Date] )
VAR EndDate = MAX ( 'Calendar_active'[Date] )
RETURN
CALCULATE (DISTINCTCOUNT ( Table_query[Item_Number] ),
      FILTER (
      ALL ( Table_query ),
      Table_query[Date_Received] <= EndDate
      && (
      ISBLANK ( Table_query[Date_Released] )
      || Table_query[Date_Released] >= StartDate
     )
  )
)

With this an item is only counted as active between its received and released dates

Thanks and regards,

Anjan Kumar Chippa

[Jah8900]

Thank you for your response. This is your measure's result:

It still looks like the items are being stacked. 

[v-achippa]

Hi @Jah8900.,

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.
Please show the expected outcome based on the sample data you provided.

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

Thanks and regards,

Anjan Kumar Chippa

[Jah8900]

I've added an update to my question. Please have a look at it. Note:
8136 has 3 reworks - all 3 need to be released for the item to be considered inactive/ released.
44862 was released the same week it was received - so it was never active
Thanks a ton in advance!

[v-achippa]

Hi @Jah8900,

Thank you for sharing the details and sample data. Looks like the issue here is that the current logic is still including items in their release week. Need to exclude those and handle items that are received and released in the same week. Please try this below measure:

Active_Items_Per_Week =
VAR EndDate = MAX ( 'Calendar_active'[Date] )
RETURN
CALCULATE (DISTINCTCOUNT ( Table_query[Item_Number] ),
FILTER (
ALL ( Table_query ),
Table_query[Date_Received] <= EndDate
&& (
ISBLANK ( Table_query[Date_Released] )
|| Table_query[Date_Released] > EndDate
)
)
)

Thanks and regards,

Anjan Kumar Chippa

[Jah8900]

Thank you so much for your response (and patience :))!

The output of your measure is this:

Whereas the correct output is:

The measure stops giving the right output in week 23.

[v-achippa]

Hi @Jah8900,

Thank you for sharing the updates. The logic for calculating active items per week is correct, but the difference you are seeing from week 23 onwards seems to be related to the data model and how reworks are recorded which is causing the count to differ.
I recommend raising a support ticket with Microsoft. So that the product team can directly review the data and help confirm the correct behaviour for your scenario.

To raise a support ticket, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks and regards,

Anjan Kumar Chippa