DAX Formula Help

[maruthisp]

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:

Measure-1: Only for Jan-23 month or the FIRST month of the YEAR, logic is below:
start_inventory = count(ID) where [Status] <> "Awaiting" group by [Department]

Measure-2: From the month Feb-23 till current_month(), logic will be
inventory = start_inventory(logic1 measure) - (count(ID) where [Status] <> "Awaiting"

                    and

                   [DecommissionDate] <= current_month() 
                    group by [Department])

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

MaruthiSP

[amitchandak · Accepted solution]

@maruthisp , You can follow the HR analytics pattern

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[maruthisp]

@amitchandak Appreciate for sharing the links and for the solution. It helped to calculate some of the measures on the future date with INACTIVE relationship definition in Data Model and use the USERELATIONSHIP function to calculate on the INACTIVE relationships for the measures.

[maruthisp]

@amitchandak Thanks for your link and solved my problem.