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
Anonymous
Not applicable

Inventory by End of Day

Hello- I seem to be having an issue with calculating the on hnd stock at the end of the day. Sample data is below

Equipment Number        Inventory_FK         Datein                 DateOut       

AM289                            23                          15/02/2019         

AM267                            34                          16/03/2019         17/03/2019

AM546                            67                          1703/2019           17/03/2019 

BC89                               89                          17/03/2019          20/03/2019

CMU6                             54                          16/03/2019           22/03/2019

AZ78                               60                          20/03/2019               

 

If the Dateout is blank, the item is still in stock. I am trying to calculate the number of items in stock at the end of each day.

Hope you can help... more data can be provided if needed.  DAX formula help 

2 REPLIES 2
TomMartens
Super User
Super User

Hey,

 

I have to admit, that I do not understand how the expected result should look like, for this reason, please provide your expected result.

Provide the expected result for each "Equipment Number" (what's the difference in comparison to "Inventory_FK") for

  • 7th of January 2019
    If this date is not applicable, please explain why
  • 18th of March 2019
  • 24th of December 2020

I also do not understand what you mean by "... items in stock by the end of each day ..."

How do you refer to each day, does your data model contain a separate Calendar table and if you mention "... by the end of each day ..." does your data contain any means to trace the development of stock available during the day. If this is not the case what is the underlying assumption for DateIn (beginning of the day or end of the day), the same question has to be answered for DateOut. 

 

Please consider to create sample data, upload the pbix and/or xlsx file to OneDrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Tom, I cannot upload sample data at this time.

 

I can create more sample data however, the result i'm looking for is the Total Stock at the end of each day. it is transactional data so the "Date in" is the item arriving in stock and "Date Out" is the item leaving.

 

E.g if i was to filter for 1 day. i would see:

- The stock at the end of the day which has a "Date Out" After the day selected.

- Or if the date is blank whcich means it is still in stock Today().

 

also, yes my model has a Calendar table...

 

Thank you,

 

If 

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.