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
        )


     

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