Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Max_WH
Frequent Visitor

a circular dependency was detected measure

Feedback Type:
Frown (Error)

Timestamp:
2023-12-22T09:33:08.3218659Z

Local Time:
2023-12-22T17:33:08.3218659+08:00

Session ID:
8bf082bf-c1ef-4518-854f-5f200520785b

Release:
November 2023

Product Version:
2.123.742.0 (23.11) (x64)

Stack Trace:
Microsoft.PowerBI.Modeler.ModelingASOperationException
at Microsoft.PowerBI.Modeler.ModelManager.<>c__DisplayClass215_0.<SaveChanges>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass6_0`1.<RunInActivity>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity(ModelingActivityKind activity, Action action)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity[T](ModelingActivityKind activity, Func`1 action)
at Microsoft.PowerBI.Modeler.ModelEditor.EditMeasureExpression(Measure measure, String expression)
at Microsoft.PowerBI.Modeling.Engine.Authoring.SchemaChangeApplier.Visit(UpdateExpressionSchemaChange schemaChange)
at Microsoft.PowerBI.Modeling.Engine.Authoring.SchemaChangeApplier.Apply(IModelManager modelManager, IEnumerable`1 changes, IRefreshPolicyUpdater refreshPolicyUpdater, IPrivateInformationService privateInformationService)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.<>c__DisplayClass27_0.<ExecuteChange>b__0(IEnumerable`1 changes)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.ExecuteChange(ModelChange modelChange, Boolean isTrustedSource, Boolean saveChange, ISchemaChangeApplierFactory schemaChangeApplierFactory, IRefreshPolicyUpdater refreshPolicyUpdater, IPrivateInformationService piiService, String descriptionToTrace)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ExtendedModelChangeScope.ExecuteChange(ModelChange modelChange, Boolean isTrustedSource, Boolean saveChange, String descriptionToTrace)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<>c__DisplayClass16_1.<RouteModelChangeAsync>b__3(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<>c__DisplayClass16_1.<RouteModelChangeAsync>b__4(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<>c__DisplayClass11_0.<RunExtendedModelChangeAsync>b__0(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<>c__DisplayClass12_0.<<RunExtendedModelChangeAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<ContinueWithAsyncOperation>d__15.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<RouteModelChangeAsync>d__16.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c.<WaitOnUIThreadForTaskCompletion>b__15_0(Task t)
at System.Threading.Tasks.ContinuationResultTaskFromTask`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass74_0.<<SynchronizeLooselyWithQueriesAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__73`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringRoutingService.<>c__DisplayClass19_0.<<RouteToLocalModel>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion[T](Task`1 task)
at Microsoft.PowerBI.Client.Windows.CommitHandler.TryCommitPendingUIEdits()
at Microsoft.PowerBI.Client.Windows.MainWindow.OnFileSaving(FileSavingEventArgs eventArgs)
at Microsoft.Practices.Prism.PubSubEvents.EventSubscription`1.<>c__DisplayClass2.<GetExecutionStrategy>b__0(Object[] arguments)
at Microsoft.Practices.Prism.PubSubEvents.EventBase.InternalPublish(Object[] arguments)
at Microsoft.PowerBI.Client.Windows.Services.EventAggregationService.<>c__DisplayClass22_0`2.<PublishAndMarshalExceptions>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.123.742.0","ModuleName":"Microsoft.PowerBI.Modeler.dll","Component":"Microsoft.PowerBI.Modeler.ModelManager+<>c__DisplayClass215_0","Error":"Microsoft.PowerBI.Modeler.ModelingASOperationException","MethodDef":"<SaveChanges>b__0 - PFE_XL_CALCCOLUMN_CIRCULAR_DEPENDENCIES","ErrorOffset":"97","ErrorCode":""}

InnerException0.Stack Trace:
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveFlags flags, Int32 maxParallelism)
at Microsoft.PowerBI.Modeler.ModelManager.<>c__DisplayClass215_0.<SaveChanges>b__0()

OS Version:
Microsoft Windows NT 10.0.19044.0 (x64 en-US)

CLR Version:
4.8 or later [Release Number = 528372]

Peak Virtual Memory:
72.6 GB

Private Memory:
1.91 GB

Peak Working Set:
1.77 GB

IE Version:
11.3636.19041.0

User ID:
59ab7ff0-29a3-4721-be97-cfff53061e0a

Workbook Package Info:
1* - en-US, Query Groups: 4, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_onObject
PBI_backstageUI
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_gitIntegration
PBI_newCard
PBI_buttonSlicerAuthoring
PBI_modelExplorer
PBI_daxQueryView
PBI_aiNarrativesVisual
PBI_qnaImproveLsdlCopilot

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
150%

Supported Services:
Power BI

1 ACCEPTED SOLUTION

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBBEsMgCEXv4poFIKgskxtkncn9rxFJW4tps3AG5s9T/9v3tCxLgsTIeZM+WANBg6K/C4IiAiKmAwInG/WhqCdXCkkJTCuYsCcCZubnznkqNXIM5GwrnpQnLvchY+D6wtY59oU/77U75y2IAkcIVTMY+o02Yeu6Ri01mpiXWcvgXlpU53pfLerfbKNe4C5pj1rKrCVwXqJFLS1qqfK/31sLRy0UtHRHgztO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Quarter", type text}, 
      {"BIFO Operational Reference Forecast Qty", Currency.Type}, 
      {"Delfor CDFRLO Qty", Currency.Type}, 
      {"Reference Forecast Qty", Currency.Type}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Cumul Delfor CDFRLO Qty", 
    (k) =>
      List.Sum(Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Delfor CDFRLO Qty] ), 
    Currency.Type
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom", 
    "Cumul Reference Forecast Qty", 
    (k) =>
      List.Sum(
        Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Reference Forecast Qty]
      ), 
    Currency.Type
  ), 
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom1", 
    "Ref vs VCD-C net", 
    each [Cumul Reference Forecast Qty] - [Cumul Delfor CDFRLO Qty], 
    Currency.Type
  ), 
  #"Added Custom3" = Table.AddColumn(
    #"Added Custom2", 
    "Cumul OP Demand Should-Be", 
    each 
      if [#"Ref vs VCD-C net"] > 0 then
        [Cumul Reference Forecast Qty]
      else
        [Cumul Delfor CDFRLO Qty], 
    Currency.Type
  ), 
  #"Added Custom4" = Table.AddColumn(
    #"Added Custom3", 
    "OP Demand Should-Be", 
    (k) =>
      try
        k[#"Cumul OP Demand Should-Be"]
          - Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Group]=k[Group]), 1){0}[
            #"Cumul OP Demand Should-Be"
          ]
      otherwise
        k[#"Cumul OP Demand Should-Be"], 
    Currency.Type
  ), 
  #"Added Custom5" = Table.AddColumn(
    #"Added Custom4", 
    "Op Demand Gap", 
    each [BIFO Operational Reference Forecast Qty] - [#"OP Demand Should-Be"], 
    Currency.Type
  )
in
  #"Added Custom5"

View solution in original post

14 REPLIES 14
v-zhangti
Community Support
Community Support

Hi, @Max_WH 

 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Iterative Calculation 

Hi, Please find the link to the excel sheet above. 

Basically, it is the iterative calculation for the 2 red-highlighted, which depend on each other starting from 2024Q1. 

 

The DAX below causing the Circular dependency error.

 

OP Demand = if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty],[Delfor CDFRLO **bleep** Qty]),if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )),[Delfor CDFRLO **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )))
)


OP Demand **bleep** = calculate(if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,max(IBPDP[BIFO Operational Reference Forecast Qty]),calculate(sum(IBPDP[BIFO Operational Reference Forecast Qty]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=0)+calculate(max([OP Demand]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]>0 && 'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]<=max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]))))

 

Let's bring your data into usable format

lbendlin_0-1703616347968.png

Then we can add the easy cumulatives

 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDcUwCATQXaivODDYZozUUfZf45tIX7aUwsUdepj7FqO1ywWSE85Ej28gggRJefAKv3T1Pap7e0goMgbSrSaOzKy3RfU+TmHQUrPXpH9FW33jIVawXMIq2P+PuUVdq3oIJUY0JGtXbvD8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"BIFO Operational Reference Forecast Qty", Currency.Type}, {"Delfor CDFRLO Qty", Currency.Type}, {"Reference Forecast Qty", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "**bleep** Delfor CDFRLO Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Delfor CDFRLO Qty]),Currency.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "**bleep** Reference Forecast Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Reference Forecast Qty]),Currency.Type), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Ref vs VCD-C net", each [**bleep** Reference Forecast Qty]-[**bleep** Delfor CDFRLO Qty],Currency.Type) in #"Added Custom2"

 

Then for the tricky part we are turning the tables and calculate the cumulative part first.

 = Table.AddColumn(#"Added Custom2", "**bleep** OP Demand Should-Be", each if [#"Ref vs VCD-C net"]>0 then [**bleep** Reference Forecast Qty] else [**bleep** Delfor CDFRLO Qty],Currency.Type) 

 

Then from that we get the quarterly demand and the gap.

 

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDcUwCATQXaivODDYZozUUfZf45tIX7aUwsUdepj7FqO1ywWSE85Ej28gggRJefAKv3T1Pap7e0goMgbSrSaOzKy3RfU+TmHQUrPXpH9FW33jIVawXMIq2P+PuUVdq3oIJUY0JGtXbvD8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"BIFO Operational Reference Forecast Qty", Currency.Type}, {"Delfor CDFRLO Qty", Currency.Type}, {"Reference Forecast Qty", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "**bleep** Delfor CDFRLO Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Delfor CDFRLO Qty]),Currency.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "**bleep** Reference Forecast Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Reference Forecast Qty]),Currency.Type), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Ref vs VCD-C net", each [**bleep** Reference Forecast Qty]-[**bleep** Delfor CDFRLO Qty],Currency.Type), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "**bleep** OP Demand Should-Be", each if [#"Ref vs VCD-C net"]>0 then [**bleep** Reference Forecast Qty] else [**bleep** Delfor CDFRLO Qty],Currency.Type), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "OP Demand Should-Be", (k)=> try k[#"**bleep** OP Demand Should-Be"]- Table.LastN(Table.SelectRows(#"Added Custom3",each [Quarter]<k[Quarter]),1){0}[#"**bleep** OP Demand Should-Be"] otherwise k[#"**bleep** OP Demand Should-Be"],Currency.Type), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Op Demand Gap", each [BIFO Operational Reference Forecast Qty]-[#"OP Demand Should-Be"],Currency.Type) in #"Added Custom5" 

 

This way you can avoid the circular reference.lbendlin_1-1703617990649.png

 

I assume you know what 

**bleep**

needs to be replaced with.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hi Ibendlin, the solution is superb! Can you advise the revised M query if there is another column "Program" in the data? i.e. to be group by "Program" like A, B, C etc.?

Thank you.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

 

A Matrix with an additional column Program (Group).

Thank you for your advice.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hi Sir,

Please find the updated sample data Iterative Circular Calculation  with column "Group". Thank you for helping.

 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBBEsMgCEXv4poFIKgskxtkncn9rxFJW4tps3AG5s9T/9v3tCxLgsTIeZM+WANBg6K/C4IiAiKmAwInG/WhqCdXCkkJTCuYsCcCZubnznkqNXIM5GwrnpQnLvchY+D6wtY59oU/77U75y2IAkcIVTMY+o02Yeu6Ri01mpiXWcvgXlpU53pfLerfbKNe4C5pj1rKrCVwXqJFLS1qqfK/31sLRy0UtHRHgztO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Quarter", type text}, 
      {"BIFO Operational Reference Forecast Qty", Currency.Type}, 
      {"Delfor CDFRLO Qty", Currency.Type}, 
      {"Reference Forecast Qty", Currency.Type}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Cumul Delfor CDFRLO Qty", 
    (k) =>
      List.Sum(Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Delfor CDFRLO Qty] ), 
    Currency.Type
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom", 
    "Cumul Reference Forecast Qty", 
    (k) =>
      List.Sum(
        Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Reference Forecast Qty]
      ), 
    Currency.Type
  ), 
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom1", 
    "Ref vs VCD-C net", 
    each [Cumul Reference Forecast Qty] - [Cumul Delfor CDFRLO Qty], 
    Currency.Type
  ), 
  #"Added Custom3" = Table.AddColumn(
    #"Added Custom2", 
    "Cumul OP Demand Should-Be", 
    each 
      if [#"Ref vs VCD-C net"] > 0 then
        [Cumul Reference Forecast Qty]
      else
        [Cumul Delfor CDFRLO Qty], 
    Currency.Type
  ), 
  #"Added Custom4" = Table.AddColumn(
    #"Added Custom3", 
    "OP Demand Should-Be", 
    (k) =>
      try
        k[#"Cumul OP Demand Should-Be"]
          - Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Group]=k[Group]), 1){0}[
            #"Cumul OP Demand Should-Be"
          ]
      otherwise
        k[#"Cumul OP Demand Should-Be"], 
    Currency.Type
  ), 
  #"Added Custom5" = Table.AddColumn(
    #"Added Custom4", 
    "Op Demand Gap", 
    each [BIFO Operational Reference Forecast Qty] - [#"OP Demand Should-Be"], 
    Currency.Type
  )
in
  #"Added Custom5"

 

Hi Ibendlin, Thank you once again! But the "**bleep** OP demand should-be" of the very first quarter is equal to "BIFO Operational Reference Forecast Qty". Can you help to enlighten?

This is taken care of here

 

#"Added Custom4" = Table.AddColumn(
    #"Added Custom3", 
    "OP Demand Should-Be", 
    (k) =>
      try
        k[#"Cumul OP Demand Should-Be"]
          - Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Group]=k[Group]), 1){0}[
            #"Cumul OP Demand Should-Be"
          ]
      otherwise
        k[#"Cumul OP Demand Should-Be"], 
    Currency.Type
  ),

via the try ... otherwise ...

Hi Ibendlin,

Thank you very much for your help! Appreciated much!

I have changed slightly the below to accomodate the "Cumul OP demand should-be" of the very first quarter equal to "BIFO Operational Reference Forecast Qty".

 

Happy new year and best wishes!

 

#"Added Custom3" = Table.AddColumn(
#"Added Custom2",
"Cumul OP Demand Should-Be",
each
if [#"Forecast Time.Forecast Quarter Indicator Num"]<=0 then
[BIFO Operational Reference Forecast Qty]
else
if [#"Ref vs VCD-C net"] > 0 then
[Cumul Reference Forecast Qty]
else
[Cumul Delfor CDFRLO Qty],
Int64.Type
),
#"Added Custom4" = Table.AddColumn(
#"Added Custom3",
"OP Demand Should-Be",
(k) =>
try
k[#"Cumul OP Demand Should-Be"]
- Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Product]=k[Product]), 1){0}[
#"Cumul OP Demand Should-Be"
]
otherwise
if k[#"Ref vs VCD-C net"] > 0 then
k[#"Cumul Reference Forecast Qty"]
else
k[#"Cumul Delfor CDFRLO Qty"],
Int64.Type
),

 

lbendlin
Super User
Super User

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

Iterative calculation in Excel 

 

Hi, Please find the link to the excel sheet above. 

Basically, it is the iterative calculation for the 2 red-highlighted, which depend on each other starting from 2024Q1. 

 

The DAX below causing the Circular dependency error.

 

OP Demand = if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty],[Delfor CDFRLO **bleep** Qty]),if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )),[Delfor CDFRLO **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )))
)


OP Demand **bleep** = calculate(if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,max(IBPDP[BIFO Operational Reference Forecast Qty]),calculate(sum(IBPDP[BIFO Operational Reference Forecast Qty]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=0)+calculate(max([OP Demand]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]>0 && 'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]<=max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]))))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.