DIO Formula - How do I sum values based on criteria from two separate fields?

Question

Hello -

I am currently attempting to calculate a 'Days Inventory Outstanding' formula, which is calculated by DIO = ((Beg. Inventory + End Inventory)/2) / COGS. In other words, I need to return the values or calculate the average of the balances for Account #13000, for the two most recent periods (periods 2 and 3).

Year | Period | Account Number | Account Description | Balance |
2020 1 11111 Acct Rec 100
2020 1 13000 Inventory 150
2020 1 21000 Acct Pay 50
2020 2 11111 Acct Rec 75
2020 2 13000 Inventory 100
2020 2 21000 Acct Pay 75
2020 3 11111 Acct Rec 100
2020 3 13000 Inventory 100
2020 3 21000 Acct Pay 100

Thank you!
Justice

Answer 1

Hi,

Are you asking for Dax fomula ? You could elaborate more about Beg. Inventory or End Inventory, currently I have found any column named like this.

I guess you are looking for expression like this :

=CALCULATE(   
AVERAGE('TblName'[Balance])' ,   
Filter(TblName'[Period] = 2 or 'TblName'[Period] = 3),  
Filter('TblName'[Account Number]=13000)  
)  

If this is not what you want, please clarify the request with more details.

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Hi @Lukas Yu -MSFT -

Unfortunately, this did not work for me. Instead of beginning inventory vs ending inventory, I am looking for the average from the most recent period vs prior period (i.e. if I use period 2 and 3, I would have to update the expression going forward each month - e.g. 3 and 4 next month).

Thanks,
Justice