Keep calculation from last months in the next ones

[Anonymous]

Hello,

Every month I have 3 columns: Stock, Forecast and a calculation RiskBBD.

I need to calculate RiskBBD in such a way that it will keep the result from previous months in the next months.

RiskBBD = Stock-Forecast, but if last month RiskBBD was negative, it will be Stock-Forecast+LastMonth RiskBBD. If In one month I won't have stock and Forecast, it will show me the result (RiskBBD) from the last month.  

Basically, if last month BBDRisk <> 0  AND <0, THEN Stock-Forecast+RiskBBD Last month,

                   if last month BBDRisk <> AND >0, Then Stock-Forecast

                      if last month<>BBDRisk 0 AND this month =0, THEN Last month RiskBBD  (The same in the next months with the last RiskBBD)

                         if last month BBDRisk =0, THEN Stock-Forecast

Here is the Calculation in Excel:

	Dec-23			Jan-24			Feb-24			Mar-24
Description	Stoc BBD	fcst	Risc BBD	Stoc BBD	fcst	Risc BBD	Stoc BBD	fcst	Risc BBD	Stoc BBD	fcst	Risc BBD
ALIVE! CALCIUM + D3 GUMMIES 60	159	355	(196)	-	278	(474)	-	176	(650)	361	257	(546)
ALPHA LIPOIC ACID 300MG SR	-	-	-	-	69	(69)	-	83	(152)	-	95	(247)
L-CARNOSINE 500MG 30 TAB ACTIVTAB	-	63	(63)	-	65	(128)	-	41	(169)	-	55	(224)
ULTRA ZEAXANTHIN 30 CAPS	-	35	(35)	-	32	(67)	-	31	(98)	-	22	(119)
TEA TREE OIL 15ML	-	-	-	-	5	(5)	-	7	(12)	-	15	(27)
INDOLE-3 SUPREME 30 CAPS VEG	-	203	(203)	-	388	(591)	-	219	(810)	-	257	(1,068)
ALIVE! PRENATAL MULTI-VITAMIN 30CPS	-	6	(6)	-	6	(12)	-	-	(12)	-	-	(12)
CAT'S CLAW 485MG 100 CAPS VEG	-	640	(640)	-	974	(1,614)	-	634	(2,248)	-	734	(2,982)
SYSTEMWELL ULTIMATE IMMUNITY 30 TAB	-	4,022	(4,022)	6,283	4,991	(2,730)	-	3,441	(6,171)	-	3,949	(10,120)
GOLDENSEAL 570MG 30 CAPS VEG	-	1,066	(1,066)	-	1,968	(3,034)	-	1,263	(4,297)	-	1,503	(5,800)
CHLORELLA MICRO-ALGAE 410MG 100 CAP	-	251	(251)	-	311	(562)	-	198	(761)	-	239	(1,000)
SELENIUM 200MCG 60 CAPS	-	8,045	(8,045)	-	7,665	(15,710)	-	4,996	(20,705)	-	5,754	(26,459)
VITAMIN D3 2000UI (ADULTI) 30 CAPS	14,437	11,808	2,629	-	11,507	(11,507)	25,160	7,782	5,872	-	8,319	(8,319)
GASTRITIX 60 CAPS VEG	-	354	(354)	-	481	(835)	-	327	(1,161)	-	365	(1,527)
HYDRAPLENISH PLUS MSM 60 CAPS VEG	873	751	122	-	1,402	(1,402)	-	1,166	(2,568)	-	1,064	(3,631)

[amitchandak]

@Anonymous , if you have date column in your table you can use time intelligence

measures similar to this can help you to build above logic

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[Anonymous]

Thank you, amitchandak! My measure should work for 3 years in future, with the above time intelligent formulas I will have to create a measure for every month and do to many if conditions.

[amitchandak]

@Anonymous , You can use year and month on row/column of Matrix