Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryszardre
Frequent Visitor

Moving result of a month as an opening balance to the next month

Hi, 

 

I would like to move summary result of one month as an opening balance to the next month. Something like that:

 Jan 2022Feb 2022Mar 2022
DAX formula A (closing balance from previous month)102028
DAX formula B25 
DAX formula C51 
DAX formula D32 
DAX formula E (Summary of A-D)2028 

 

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?

3 REPLIES 3
v-xiaotang
Community Support
Community Support

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.

PurpleGate
Resolver III
Resolver III

Hi,

 

This was my starting table:

PurpleGate_0-1657093497805.png

 

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])))
PurpleGate_1-1657093553097.png

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])

 PurpleGate_2-1657093611668.png

 

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))
PurpleGate_3-1657093661463.png

 

Then I added them together

ItemsStartNewMonth = CALCULATE([ItemsCurrentMonth]+[ItemsLastMonth],FILTER('Date','Date'[Date]))

 PurpleGate_4-1657093686940.png

 

 



 

 

amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors