Monthly cost and revenue where revenue = monthly total less previous month's value for Accrual

[Vanessa88]

Hi All, 

I need to calculate monthly total cost and revenue to create a high level graph comparing both for finacial report 

Requirements: 

Cost =  NZ & AU Jobs cost + NZ & AU Discretionary

Revenue = NZ & AU Consulting + NZ AU Software

Here's my requirement to calculate the REVENUE: 

If (Account Type = "AU Consulting" , MONTHLY TOTAL SHOUD BE THE SUM OF THE CURRENT MONTH AU CONSULTING LESS PREVIOUS MONTHS' ACCRUAL VALUE under AU Consulting. ALSO IF (Account Type = "NZ Consulting", MONTHLY TOTAL SHOUD BE THE SUM OF THE CURRENT MONTH NZ CONSULTING LESS PREVIOUS MONTHS' ACCRUAL VALUE under NZ Consulting. 

This is what my data sets look like. I can calculate it manually in excle for the life of me I can't fathom the formula to do this in Power BI.  Can anyone help me please? Really appreciate your help. Thanks! 

Item	Account	Account Type	Job Name	Job No	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22
Cost	AU	AU Job Cost	MNB	7692	1000	500	200	200	800	200	100
Cost	AU	AU Job Cost	Elyssa	7717	1000	500	200	200	800	200	100
Cost	AU	Finance	AU Discretionary	20	1000	500	200	200	800	200	100
Cost	NZ	Finance	NZ Discretionary	10	1000	500	200	200	800	200	100
Cost	NZ	NZ Job Cost	CBD	3176	1000	500	200	200	800	200	100
Cost	NZ	NZ Job Cost	ALDS	3500	1000	500	200	200	800	200	100
Cost	NZ	NZ Job Cost	Light	3612	1000	500	200	200	800	200	100
Cost	NZ	NZ Job Cost	HHS	3678	1000	500	200	200	800	200	100
Revenue	AU	AU Consulting	ACCRUALS	1	1000	500	200	200	800	200	100
Revenue	AU	AU Consulting	SWD	7602	1000	500	200	200	800	200	100
Revenue	AU	AU Consulting	HMN	7680	1000	500	200	200	800	200	100
Revenue	AU	Finance	AU Software	20	1000	500	200	200	800	200	100
Revenue	NZ	Finance	NZ Software	10	1000	500	200	200	800	200	100
Revenue	NZ	NZ Consulting	ACCRUALS	1	1000	500	200	200	800	200	100
Revenue	NZ	NZ Consulting	CCR	3295	1000	500	200	200	800	200	100
Revenue	NZ	NZ Consulting	APR	3500	1000	500	200	200	800	200	100
Revenue	NZ	NZ Consulting	AMS	3564	1000	500	200	200	800	200	100
Revenue	NZ	NZ Consulting	CPS	3572	1000	500	200	200	800	200	100
Total Cost	AU& NZ				8000	4000	1600	1600	6400	1600	800
Total Revenue	AU				4000	1500	600	600	2400	600	300
Total Revenue	NZ				6000	2000	700	1000	4600	400	400
Total Revenue	AU& NZ				10000	3500	1300	1600	7000	1000	700

Cheers,
Vanee

[v-jianboli-msft]

Hi @Vanessa88 ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about how to calculate the revenue.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.