Need help with circular dependency in measure that needs to use itself to produce the correct value

[Aldimar_Junior]

Hello everyone! hope yall doing great..

I need help returning a dax measure based on the value of the previous i.e:

1) My current table goes as follows:

• TOTAL_SALES = SUM of all sales
• TOTAL_COST = SUM of product related expenses
• TOTAL_EXPENSES = SUM of office related expenses
• TOTAL_PROFIT = (TOTAL_SALES) - (TOTAL_COST + TOTAL_EXPENSES)

Note: Every value comes from a single field in my DB -> "VALUE", for every measure I calculate "VALUE" using a filter of "TYPE" i.e:
TOTAL_SALES = CALCULATE( SUM( VALUE ), TYPE = "SALES" )... and so on.

The types are -> "1 - SALES", "2 - COST", "3 - EXPENSES"

This gives me the following:

OFFICE ID |	MONTH |	TOTAL_SALES |	TOTAL_COST |	TOTAL_EXPENSES |	TOTAL_PROFIT |
01	JUN	14.260.264	9.006.039	2.074.296	3.179.929
01	JUL	14.855.894	1.800.763	1.362.679	11.692.452
01	AUG	14.188.573	3.343.291	1.408.309	9.436.973

The problem I'm facing is: I need to calculate "TOTAL_SALES" using the "TOTAL_PROFIT" of the last month as a starting point and if there's no "last month" I need to use a given value instead i.e:

"Default start value = 20.000.000"
"last_month_val = TOTAL_PROFIT(LAST_MONTH)

JUN has no "last_month_val" use "Default start value"
TOTAL_SALES = CALCULATE( SUM( VALUE ), TYPE = "SALES") + last_month_val

The thing I cant figure out/get working is the "last_month_val" part.... I already tried "PREVIOUSMONTH", CALCULATE using DATEADD(DATE, -1, MONTH) as filter, CALCULATE using EDATE(DATE, -1)...

2) To "compile it all" the desired result would be: 

• JUN starts with +20.000.000 since theres no previous month to look at.
• NEW_TOTAL_SALES = TOTAL_SALES + PREVIOUS_MONTH.
• TOTAL_COST and TOTAL_EXPENSES stays the same.
• TOTAL_PROFIT is now calculated based on the new "TOTAL_SALES" value.

OFFICE ID |	MONTH |	PREVIOUS_MONTH |	TOTAL_SALES |	NEW_TOTAL_SALES |	TOTAL_COST |	TOTAL_EXPENSES |	TOTAL_PROFIT |
01	JUN	20.000.000	14.260.264	34.260.264	9.006.039	2.074.296	23.179.929
01	JUL	23.179.929	14.855.894	38.035.823	1.800.763	1.362.679	34.872.381
01	AUG	34.872.381	14.188.573	49.060.954	3.343.291	1.408.309	44.309.354

Can anyone give me a hand/direction to follow?

[Aldimar_Junior]

I created a xlsx with the expected result I need, The formulas/Calculations used and some sample data:

Expected result.xlsx 

[Aldimar_Junior]

Update, I got "most" of it working....

The formula goes like this:

• sum_prev_curr = val_previous_month + val_curr_month
• profit (green - red) = sum_prev_curr - ( total_expenses(prod) + total_expenses(off) )

The problem.. val_previous_month should be the value of profit (green - red) from the previous month (as said by the name) but when I try return the value of profit (green - red) "-1 month" into val_previous_month I get circular dependencies errors... heres what I have currently:

val_previous_month =

_def_initial_val = 20000000

// TIME INTELLIGENCE --------------------------------------------------------------------------
// Return the value of the "lowest" month available in the database
VAR _min_month = MONTH(DATEVALUE(CALCULATE(
    MIN('Table Name (X)'[Order Date]),
    ALL('Table Name (X)')
)))

// Return the value of the current selected month (per row)
VAR _this_month = SELECTEDVALUE('Table Name (X)'[Order Date].[MonthNo])

// Return the value of the current selected month - 1 [PREVIOUS] (per row)
VAR _prev_month = (_this_month - 1)

// Verify if selected month is the lowest month in the DB
VAR _verify = IF(_this_month = _min_month, "Lowest", "-")
// --------------------------------------------------------------------------------------------

// Return the value of "profit" from previous month
VAR _prev_month_profit = 
CALCULATE([profit (green - red)],
    FILTER(ALL('Table Name (X)'), 
        'Table Name (X)'[Order Date].[MonthNo] = _prev_month
    )
)

// If the selected month is the lowest, return a sum of "total sales" + "initial value"
// else return the value of "profit" from previous month
VAR _sumif = IF(_verify = "Lowest",
    [total_sales] + _def_initial_val,
    _prev_month_profit
)

RETURN
    _sumif

I really don't know how to solve this issue with circular dependency...