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

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

1. 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