Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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"
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.
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
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.
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
),
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]))))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
12 | |
12 | |
9 |