Circular Dependency Error in Power BI Measure

[Mastermayank26]

HI All,

I am trying to recreate excel formula in Power BI which is causing circular dependency in the measure.Please find below my scenario:

I have a table with MMYYY, Total Sales,

MMYY	TotalSales	Previous Month Sales
Jan-25	100	0
Feb-25	200	100
Mar-25	300	200
Apr-25	400	300
May-25	0	400
Jun-25	0	0
Jul-25	0	0

We have sales data untill Apr 2025 but May,June and July sales data should be calculated on the basis of Previous month sales as follows:

ForecastSales= PreviousMonth Sales +100

MMYY	TotalSales	Previous Month Sales
Jan-25	100	0
Feb-25	200	100
Mar-25	300	200
Apr-25	400	300
May-25	500	400
Jun-25	600	500
Jul-25	700	600

When i am calculating it in Power BI it creates circular dependency as Previous sales being calculted from total sales and vice Versa.

In the excel its easy just referrng the cell, how to achieve in Power BI?

[v-priyankata]

Hi @Mastermayank26 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Mastermayank26 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Mastermayank26 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[techies]

Hi @Mastermayank26 please check this forecast measure

Forecast Sales =

VAR CurrentMonth = MAX('Date'[Date])

VAR LastActualMonth =

    CALCULATE (

        MAX('Date'[Date]),

        FILTER(

            ALL('Date'),

            NOT(ISBLANK([Total Sales]))

        )

    )

VAR MonthsAhead =

    DATEDIFF(LastActualMonth, CurrentMonth, MONTH)

VAR LastActualSales =

    CALCULATE (

        [Total Sales],

        'Date'[Date] = LastActualMonth

    )

RETURN

    IF (

        ISBLANK([Total Sales]) && MonthsAhead > 0,

        LastActualSales + (MonthsAhead * 100),

        [Total Sales]

    )

[Mastermayank26]

Hi Thank you for the quick response, it did work on given example but not working on my problem as I need to calculate previous month sales explicitly due to some other calculations also involved. 

Can you help us where exactly you are calculating previous months sales here??

[techies · Accepted solution]

Hi @Mastermayank26 i tried implementing this, almost there.... I guess the issue might be in the last section where it calculates the forecasted sales after the last sales month. Could you take a look?

Forecast Sales  =

VAR CurrentMonth = MAX('Date'[Date])

-- Get previous month's start and end dates

VAR PrevMonthStart =

    DATE(YEAR(CurrentMonth), MONTH(CurrentMonth) - 1, 1)

VAR PrevMonthEnd =

    EOMONTH(PrevMonthStart, 0)

-- Calculate Total Sales for the previous month

VAR PrevMonthSales =

    CALCULATE(

        [Total Sales],

        FILTER(

            ALL('Date'),

            'Date'[Date] >= PrevMonthStart && 'Date'[Date] <= PrevMonthEnd

        )

    )

-- Get the last month of actual sales data

VAR LastSalesMonth =

    CALCULATE(

        MAX('Date'[Date]),

        FILTER(

            ALL('Date'),

            [Total Sales] > 0

        )

    )

VAR MonthsAhead =

    DATEDIFF(LastSalesMonth, CurrentMonth, MONTH)

RETURN

    IF (

        ISBLANK(PrevMonthSales) && MonthsAhead > 0,

        PrevMonthSales + 100 * MonthsAhead  ,

        PrevMonthSales

    )

[techies]

Hi @Mastermayank26 i tried implementing this, almost there.... I guess the issue might be in the last section where it calculates the forecasted sales after the last sales month. Could you take a look?

Forecast Sales  =

VAR CurrentMonth = MAX('Date'[Date])

-- Get previous month's start and end dates

VAR PrevMonthStart =

    DATE(YEAR(CurrentMonth), MONTH(CurrentMonth) - 1, 1)

VAR PrevMonthEnd =

    EOMONTH(PrevMonthStart, 0)

-- Calculate Total Sales for the previous month

VAR PrevMonthSales =

    CALCULATE(

        [Total Sales],

        FILTER(

            ALL('Date'),

            'Date'[Date] >= PrevMonthStart && 'Date'[Date] <= PrevMonthEnd

        )

    )

-- Get the last month of actual sales data

VAR LastSalesMonth =

    CALCULATE(

        MAX('Date'[Date]),

        FILTER(

            ALL('Date'),

            [Total Sales] > 0

        )

    )

VAR MonthsAhead =

    DATEDIFF(LastSalesMonth, CurrentMonth, MONTH)

RETURN

    IF (

        ISBLANK(PrevMonthSales) && MonthsAhead > 0,

        PrevMonthSales + 100 * MonthsAhead  ,

        PrevMonthSales

    )

[Mastermayank26]

Hi @techies 

The example i gave in the questions is just for reference, I want to calculate Total Sales recursively for next 4-6 months. Do you know how to calculate it recursively? 

E.g I have data till March 2025 then it will show actual sales
March Sales =100

April it will show Sales = Actual Sales +Previous Month sales 
May Sales would be =Actual Sales + Previous Month sales

[techies]

ok, this is tricky

[bhanu_gautam]

@Mastermayank26 Create a Calculated Column for Previous Month Sales: This column will hold the sales of the previous month.

DAX
PreviousMonthSales =
VAR CurrentMonth = 'Table'[MMYY]
RETURN
CALCULATE(
MAX('Table'[TotalSales]),
FILTER(
'Table',
'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
)
)

Create a Measure for Forecast Sales: This measure will calculate the forecast sales based on the previous month’s sales

DAX
ForecastSales =
VAR CurrentMonth = MAX('Table'[MMYY])
VAR PreviousMonthSales =
CALCULATE(
MAX('Table'[TotalSales]),
FILTER(
'Table',
'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
)
)
RETURN
IF(
ISBLANK(MAX('Table'[TotalSales])),
PreviousMonthSales + 100,
MAX('Table'[TotalSales])
)

Create a Calculated Column for Total Sales Including Forecast: This column will include the forecasted sales for the months where actual sales data is not available.

DAX
TotalSalesIncludingForecast =
VAR CurrentMonth = 'Table'[MMYY]
VAR PreviousMonthSales =
CALCULATE(
MAX('Table'[TotalSales]),
FILTER(
'Table',
'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
)
)
RETURN
IF(
ISBLANK('Table'[TotalSales]),
PreviousMonthSales + 100,
'Table'[TotalSales]
)

By using these steps, you avoid the circular dependency issue by separating the calculation of previous month sales and forecast sales into different columns and measures. This way, you can achieve the desired result in Power BI.

[Mastermayank26]

Hi @bhanu_gautam 

Thanks for the response, I dont want to use calculated column due to various reasons, is there anyway to achieve using measures only?