Forum Discussion
Events in Progress Problem
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:
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
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 🙂
14 Replies
hi
I think You are doing summarize by Weeks, so the date column will breakout your data, need remove it.
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.
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
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
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 🙂
