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 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]
    )