Forum Discussion
Super UserCalculate the Measures
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