Forum Discussion
How to overcome circular dependency while leveraging same measure?
I have a table that has Number of products in last 24 hrs column, Number of sold products and Number of products in inventory. I need to calculate Number of products.
Following is my table:
| Number of products in last 24 hrs | Number of sold products | Number of products in inventory | |
| 2023-08-01 | 1 | 0 | 0 |
| 2023-08-03 | 5 | 14 | 16 |
| 2023-08-04 | 2 | 15 | 22 |
| 2023-08-08 | 4 | 17 | 25 |
| 2023-08-11 | 3 | 9 | 27 |
| 2023-08-12 | 7 | 22 | 26 |
I need to create a DAX query for Number of products measure
Desired result:
| Number of products in last 24 hrs | Number of sold products | Number of products in inventory | Number of products | |
| 2023-08-01 | 1 | 0 | 0 | 1 |
| 2023-08-03 | 5 | 14 | 16 | (5-(14+16)+1) = -24 |
| 2023-08-04 | 2 | 15 | 22 | (2-(15+22)+(-24)) = -59 |
| 2023-08-08 | 4 | 17 | 25 | (4-(17+25)+(-59)) = -97 |
| 2023-08-11 | 3 | 9 | 27 | (3-(9+27)+(-97)) = -130 |
| 2023-08-12 | 7 | 22 | 26 | (7-(22+26)+(-130)) = -171 |
Hi samk_49 ,
Please try to use this DAX to create a new column:Number of products = CALCULATE( SUM(Sheet12[Number of products in last 24 hrs]) - SUM(Sheet12[Number of sold products]) - SUM(Sheet12[Number of products in inventory]), FILTER( 'Sheet12', 'Sheet12'[Date] < EARLIER(Sheet12[Date]) ) ) + 'Sheet12'[Number of products in last 24 hrs] - 'Sheet12'[Number of products in inventory] - 'Sheet12'[Number of sold products]The final output is below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi samk_49 ,
Please try to use this DAX to create a new column:Number of products = CALCULATE( SUM(Sheet12[Number of products in last 24 hrs]) - SUM(Sheet12[Number of sold products]) - SUM(Sheet12[Number of products in inventory]), FILTER( 'Sheet12', 'Sheet12'[Date] < EARLIER(Sheet12[Date]) ) ) + 'Sheet12'[Number of products in last 24 hrs] - 'Sheet12'[Number of products in inventory] - 'Sheet12'[Number of sold products]The final output is below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.samk_49 , Create a new measure using the current measure and try
Sumx(Values(Records[Date]), [PrevProd_Test])
My apologies Amit, your code worked well for originally posted question. But, I had to change my question to provide better clarity.
