Forum Discussion

maruthisp's avatar
maruthisp
Super User
3 years ago
Solved

DAX Formula Help

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:

IDDepartmentInventoryDateDecommisonDateStatus
111A1/01/202330/01/2023Approved
222A

1/02/2023

 

 New
333A

1/03/2023

 

 Awaiting
444B1/01/2023 New
666B1/02/2023 New
777B1/02/202328/02/2023Approved
888B1/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.

MonthDep-ADep-B
  Jan-23       138        46
Feb-2314048
Mar-2313847
Apr-2313847
May-2313847
Jun-2313647
Jul-2313647
Aug-2313647
Sep-2313643
Oct-2313643
Nov-2313643
Dec-2313443

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

 

DAX

3 Replies