Recreate Historical Inventory with Production Report

[Lightsoutflyer3]

I am trying to recreate historical inventory from a production report.  The production report has a Task ID, Work type, Received Date, Processed Date, and Inbox.

• I want to capture inventory for each day.  Example screenshot attached.
• If a work item is received on 1/1/2025 and is processed on 1/1/2025, the work item should be captured as inventory for 1/1/2025 and then removed 1/2/2025.
• I cannot match the manually created historical numbers.

Below is the a version of the measure that I am trying to get to work.  

Daily Inventory = 

VAR TargetDate = Max('Calendar'[Date])

RETURN

CALCULATE(

     COUNTROWS('ProductionTable',

     FILTER('ProductionTable',

     'ProductionTable'[ReceivedDate] <= TargetDate &&

     (ISBLANK('ProductionTable'[ProcessedDate]) || 'ProductionTable'[ProcessedDate] > TargetDate)))

[v-pnaroju-msft]

Hi Lightsoutflyer3,

Can you please confirm if your issue has been resolved? If yes, you are welcome to share your solution and mark it as solved so that other users can benefit too. This will help other community members with similar problems to find a solution faster.

If we do not hear from you, we will close this thread. If you need any further help in the future, please reach out through the Microsoft Fabric Community Forum and create a new thread. We will be happy to assist you.

Thank you.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We are reaching out to check if your query has been resolved. If you have found a solution, we would greatly appreciate it if you could share it with the community to assist others encountering the same issue.

If the problem still persists, please provide sample data that clearly demonstrates your issue or question in a usable format (please avoid screenshots). Make sure the data does not contain any sensitive or irrelevant information. Additionally, please specify the expected result based on the sample data you provide.

Thank you.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We are writing to follow up on whether your query has been resolved. If you have found a solution, we kindly request you to share it with the community to help others who may be facing a similar issue.

If the issue remains unresolved, please provide sample data that comprehensively illustrates your problem or question in a usable format (not as a screenshot). Kindly ensure that no sensitive or unrelated information is included.Please also indicate the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution, as this helps the broader community.
If the issue remains unresolved, please provide sample data that comprehensively illustrates your problem or question in a usable format (not as a screenshot). Kindly ensure that no sensitive or unrelated information is included.Please also indicate the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[Lightsoutflyer3]

Thank you for the update.  I incorporated the updates and still do not get the results that I am looking for.  The inventory numbers being returned is almost doubles my target number.  I will have to review my data set.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi Lightsoutflyer3,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @Deku, for your response.

Hi Lightsoutflyer3,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file, which may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will greatly help other community members who may have similar queries.

Thank you.

[Deku]

Daily Inventory = 

VAR TargetDate = Max('Calendar'[Date])

RETURN

CALCULATE(

COUNTROWS('ProductionTable'),

FILTER(

ALL(

'ProductionTable'[ReceivedDate],

'ProductionTable'[ProcessedDate]

),

'ProductionTable'[ReceivedDate] <= TargetDate &&

(

ISBLANK('ProductionTable'[ProcessedDate]) ||

'ProductionTable'[ProcessedDate] > TargetDate)))

Added a ALL() to the filter to remove the effect of the calendar table, and limited down to just the columns to reduce table materialization

[Lightsoutflyer3]

The additional ALL came back with the same totals for my graph.  I might need to adjust something else.

Thank you for reviewing.