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