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,

MMYYTotalSalesPrevious Month Sales
Jan-251000
Feb-25200100
Mar-25300200
Apr-25400300
May-250400
Jun-2500
Jul-2500

 


We have sales data untill Apr 2025 but May,June and July sales data should be calculated on the basis of Previous month sales as follows:

ForecastSales= PreviousMonth Sales +100


MMYYTotalSalesPrevious Month Sales
Jan-251000
Feb-25200100
Mar-25300200
Apr-25400300
May-25500400
Jun-25600500
Jul-25700600



When i am calculating it in Power BI it creates circular dependency as Previous sales being calculted from total sales and vice Versa.

In the excel its easy just referrng the cell, how to achieve in Power BI?



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


     

11 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Mastermayank26 Create a Calculated Column for Previous Month Sales: This column will hold the sales of the previous month.

    DAX
    PreviousMonthSales =
    VAR CurrentMonth = 'Table'[MMYY]
    RETURN
    CALCULATE(
    MAX('Table'[TotalSales]),
    FILTER(
    'Table',
    'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
    )
    )

     

    Create a Measure for Forecast Sales: This measure will calculate the forecast sales based on the previous month’s sales

    DAX
    ForecastSales =
    VAR CurrentMonth = MAX('Table'[MMYY])
    VAR PreviousMonthSales =
    CALCULATE(
    MAX('Table'[TotalSales]),
    FILTER(
    'Table',
    'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
    )
    )
    RETURN
    IF(
    ISBLANK(MAX('Table'[TotalSales])),
    PreviousMonthSales + 100,
    MAX('Table'[TotalSales])
    )

     

    Create a Calculated Column for Total Sales Including Forecast: This column will include the forecasted sales for the months where actual sales data is not available.

    DAX
    TotalSalesIncludingForecast =
    VAR CurrentMonth = 'Table'[MMYY]
    VAR PreviousMonthSales =
    CALCULATE(
    MAX('Table'[TotalSales]),
    FILTER(
    'Table',
    'Table'[MMYY] = EOMONTH(CurrentMonth, -1)
    )
    )
    RETURN
    IF(
    ISBLANK('Table'[TotalSales]),
    PreviousMonthSales + 100,
    'Table'[TotalSales]
    )

     

    By using these steps, you avoid the circular dependency issue by separating the calculation of previous month sales and forecast sales into different columns and measures. This way, you can achieve the desired result in Power BI.

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

      Hi bhanu_gautam 

      Thanks for the response, I dont want to use calculated column due to various reasons, is there anyway to achieve using measures only?


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


         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mastermayank26 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mastermayank26 
    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mastermayank26 
    I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
    Thank you.