Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I would like to move summary result of one month as an opening balance to the next month. Something like that:
Jan 2022 | Feb 2022 | Mar 2022 | |
DAX formula A (closing balance from previous month) | 10 | 20 | 28 |
DAX formula B | 2 | 5 | |
DAX formula C | 5 | 1 | |
DAX formula D | 3 | 2 | |
DAX formula E (Summary of A-D) | 20 | 28 |
I use for "DAX formula A" PREVIOUSMONTH formula but then I get circular dependency between "DAX formula E" (Summary of A-D) and "DAX formula A".
"DAX formulas B-D" are calculations of sales, new orders so it is not taken directly from any table, it is also calculated.
Could anybody help?
Hi @ryszardre
I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.
If you need more help, please let me know.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This was my starting table:
I created a measure to see how many products I had on hand
Number of Products on Hand = CALCULATE(SUM('Product'[AmountAdded]),FILTER('Date','Date'[Date] <=maxx('Date','Date'[Date])))-CALCULATE(SUM('Product'[AmountSold]),FILTER('Date','Date'[Date] <= maxx('Date','Date'[Date])))
Then I created a measure using the above measure to see how many products I had in the current month
ItemsCurrentMonth = TOTALMTD('Product'[Number of Products on Hand],'Date'[Date])
Then I created a measure to say how many products I had the previous month
ItemsLastMonth = TOTALMTD('Product'[Number of Products on Hand],DATEADD('Date'[Date],-1,MONTH))
Then I added them together
ItemsStartNewMonth = CALCULATE([ItemsCurrentMonth]+[ItemsLastMonth],FILTER('Date','Date'[Date]))
@ryszardre , we can not refresh the previous row of column, calculated run time like excel
b7= b6+a7
So we need to build using some other logic like cumulative
example
Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |