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

 

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
        )


     

    • 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

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