cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
depple
Helper III
Helper III

Inventory calculation - Based on expiry date

Hi,

 

I am trying to create a weekly inventory report, but I am having difficulties creating a measure that adjusts inventory for expiry date. Below are two example tables that show my structure.

 

tblBatch 

Item | Qty |Expiry Date

Alfa 10 05.09.2019

Alfa 25 10.09.2019

Bravo 100 20.09.2019

 

tblCalendar

Date | Week

01.09.2019 201935

02.09.2019 201936

03.09.2019 201936

[...]

20.09.2019 201938

 

This I what I am trying to get the output to be like, where inventory is reduced as items pass their expiry date:

 

rptInventory

                                   Week

Item    201935    201936    201937    201938

Alfa         35            25             0              0

Bravo    100           100          100            0

 

Any suggestions on how to build the measure would be deeply appreciated.

 

/depple

1 ACCEPTED SOLUTION

I found a solution using the CALCULATE function, with expiry date as filter.

 

Thanks to all that took their time to read my issue.

 

/depple

View solution in original post

6 REPLIES 6
RobbeVL
Impactful Individual
Impactful Individual

Hi,

 

you can simly drag everything to a Matrix Table:

 

Capture.JPG

@RobbeVL 

 

Thank you for your suggestion. I have tried this, but it does not return the output that I need. This solutuin plots when which quantity expire, whereas I want the measure to return the non-expired inventory for each week, like in the example I showed in my intial post.

 

/depple 

RobbeVL
Impactful Individual
Impactful Individual

Ok,

Your explenation was not very clear. still isnt.
When is the expiery date? When is the trigger?

Sorry if I am being unclear. I will try to correct this.

 

This is the desired output of the measure given the data in the two tables I provided in the initial post:

 

                                   Week

Item    201935    201936    201937    201938

Alfa         35            25             0              0

Bravo    100           100          100            0

 

For each future week, non-expired batches are summed. For the week that a batch has the expiry date, it will not be included in the inventory, for example the 10 Alfa that has expiry date 5/9-2019 (Week 201936) are not included in Week 201936, as they expire that week. The remaining 25 Alfa expire in 10/9-19, and therefore from 201937 and forwards, there a 0 Alfa in inventory.

 

Again, sorry for being unclear. I hope that this is a better explanation.

 

/depple

 

 

 

 

 

 

I found a solution using the CALCULATE function, with expiry date as filter.

 

Thanks to all that took their time to read my issue.

 

/depple

View solution in original post

hi depple,

 

can you give us the example where you use the filter?

 

i have also tried to do the same with calculate (distinctcount(field1),filter(allselected(table), [expirydate] >= [i still havent figured out what i should put in here]))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.