Forum Discussion

Mastermayank26's avatar
Mastermayank26
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago
Solved

Circular Dependency Error in Power BI Measure

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, MM...
  • techies's avatar
    techies
    1 year ago

    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
        )


     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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's avatar
Mastermayank26
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago

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?