Calculate the Measures

maruthisp — Thu, 09 Mar 2023 12:47:11 GMT

Hi Everyone,
Good Morning! I am newbie to DAX and struggling to derive the below scenario and would like to ask for help in this Power BI Forum. Below is the sample table structure and sample data how it looks and Measure Business logics to derive.
I have a table with columns as below:

ID	Department	InventoryDate	DecommisonDate	Status
111	A	1/01/2023	30/01/2023	Approved
222	A	1/02/2023		New
333	A	1/03/2023		Awaiting
444	B	1/01/2023		New
666	B	1/02/2023		New
777	B	1/02/2023	28/02/2023	Approved
888	B	1/03/2023		Awaiting

Sample Output with more months data just to check how the graph looks. From JAN-23 towards rest of the months, values will be decreasing and graph chart should be going to down.

Month	Dep-A	Dep-B
Jan-23	138	46
Feb-23	140	48
Mar-23	138	47
Apr-23	138	47
May-23	138	47
Jun-23	136	47
Jul-23	136	47
Aug-23	136	47
Sep-23	136	43
Oct-23	136	43
Nov-23	136	43
Dec-23	134	43

Above Final Output to display in the chart as below:

My requirements are below for DAX calculations is:

So for Feb figures above:
Measure-1:
month = max(month([Inventory Date])) - 1
start_inventory = count(*) where [C7 Status] <> "3. Awaiting Approval" group by [Department]

For subsequent months:

Measure-2:
month = previous month + 1
inventory = start_inventory - (count(*) where [C7 Status] <> "3. Awaiting Approval" and [Decommission Date] <= current_month group by [Department])

Please let me know if any further details and thanks in advance.
Best Regards,

MaruthiSP

topic Calculate the Measures in DAX Commands and Tips

Calculate the Measures