Opening and closing balance calculations

[medkadri10]

Hi Team,

i want to calculte the opeging & closing balance by DAX in Power BI,

I have created the closing balance of first Week 30: 

Cloasing Balance Week 30 = CALCULATE(SUM(DATA[Balance USD]), DATA[INCOME/EXPENSES  ]="Closing Balance")

Income_USD = CALCULATE(SUM(DATA[Balance USD]), DATA[INCOME/EXPENSES  ]="INCOME")

Expenses_USD = CALCULATE(SUM(DATA[Balance USD]), DATA[INCOME/EXPENSES  ]="EXPENSES")

So the closing Balance of week 30 is the opening balance of Week 31

the closing Balance of week 31 is the opening balance of Week 32
like the table excel :

Row Labels	Closing Balance	INCOME	EXPENSES	Closing Balance
WEEK 30	423 232			423 232
WEEK 31	423 232	219 646	-    465 967	176 912
WEEK 32	176 912	34	-      49 744	127 202
WEEK 33	127 202	509 788	-    535 240	101 750

Spoiler

Thanks

file 

[medkadri10]

Thank you 

[mickey64 · Accepted solution]

Step 0: I use these data.

Step 1: I make some measures below.

M_WeekNo = WEEKNUM(MAX('Table'[Row Labels]))

M_Opening = SUMX(FILTER('Table','Table'[INCOME/EXPENSES]="Opening"),'Table'[Balance])

M_Income = SUMX(FILTER('Table','Table'[INCOME/EXPENSES]="INCOME"),'Table'[Balance])

M_Expenses = SUMX(FILTER('Table','Table'[INCOME/EXPENSES]="EXPENSES"),'Table'[Balance])

M_Opening RT = CALCULATE([M_Opening],FILTER(ALL('Table'),'Table'[Row Labels]<=MAX('Table'[Row Labels])))

M_Income RT = CALCULATE([M_Income],FILTER(ALL('Table'),'Table'[Row Labels]<=MAX('Table'[Row Labels])))

M_Expenses RT = CALCULATE([M_Expenses],FILTER(ALL('Table'),'Table'[Row Labels]<=MAX('Table'[Row Labels])))

M_Closing Balance = [M_Opening RT]+[M_Income RT]+[M_Expenses RT]

M_Opening Balance = CALCULATE([M_Closing Balance],ALL('Table'),DATEADD('Table'[Row Labels],-7,DAY))

Step 2: I make a matrix.

[medkadri10]

Need Help