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
        )


     

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

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's avatar
techies
Icon for Super User rankSuper User
1 year ago

ok, this is tricky