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

Justice Baird 21 Reputation points
2020-12-10T14:48:57.523+00:00

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

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,243 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-12-11T06:21:28.043+00:00

    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

    0 comments No comments

  2. Justice Baird 21 Reputation points
    2020-12-11T17:21:12.887+00:00

    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