measure for recursive approach

[PBIDev01]

Hi community,

Im trying to calculate ProjectedStock, but I'm facing a recursive issue :

in the period or the same period as the extraction (for example 202508==period of extracted data is 22/08/2025) :

projectedstock = [V1]+[V2]+Stock_At_Time-[V3+V4].

Till here everything is ok, the problem appears in the calculation of the ProjectedStock for the next periods because the Stock_At_Time should be taken as the ProjectedStock[Period-1]:

for example :

ProjStock(202511) = [V1]+[V2]+ProjectedStock[202510]-[V3+V4].

and

ProjStock(202510) = [V1]+[V2]+ProjectedStock[202509]-[V3+V4].

and

ProjStock(202509) = [V1]+[V2]+ProjectedStock[202508]-[V3+V4].

So, how to do so without having a circular dependencies or recursive issues.

Thank you.

[bhanu_gautam · Accepted solution]

@PBIDev01 First, calculate the ProjectedStock for the initial period (e.g., 202508) using a calculated column. This will serve as the base for subsequent calculations.

ProjectedStock_Initial = [V1] + [V2] + Stock_At_Time - ([V3] + [V4])

Use a calculated table to iterate over the periods and calculate the ProjectedStock for each subsequent period. This approach avoids circular dependencies by storing intermediate results in a separate table.

ProjectedStockTable =
VAR BasePeriod = 202508
VAR MaxPeriod = 202511
RETURN
GENERATE(
FILTER(
ADDCOLUMNS(
GENERATESERIES(BasePeriod, MaxPeriod, 1),
"ProjectedStock",
VAR CurrentPeriod = [Value]
VAR PreviousPeriodStock =
IF(CurrentPeriod = BasePeriod,
[V1] + [V2] + Stock_At_Time - ([V3] + [V4]),
CALCULATE(
MAX(ProjectedStockTable[ProjectedStock]),
ProjectedStockTable[Period] = CurrentPeriod - 1
)
)
RETURN
PreviousPeriodStock + [V1] + [V2] - ([V3] + [V4])
),
[Value] >= BasePeriod
),
ROW("Period", [Value])
)

[danextian · Accepted solution]

It would be easier for us to come up with a solution if you just provided a sample pbix with confidential data removed.

[Shahid12523 · Accepted solution]

Use this

ProjectedStock =
VAR CurrPeriod = MAX(FactStock[Period])
VAR BasePeriod = 202508 // extraction period
VAR StartStock =
CALCULATE(MAX(FactStock[Stock_At_Time]), FactStock[Period] = BasePeriod)
VAR Movements =
SUMX (
FILTER ( FactStock, FactStock[Period] <= CurrPeriod && FactStock[Period] > BasePeriod ),
FactStock[V1] + FactStock[V2] - FactStock[V3] - FactStock[V4]
)
RETURN
StartStock + Movements

[AmiraBedh · Accepted solution]

Hello !

I don't think that you need recursion here what I understood is that you need an anchored running balance. (please correct me if I am wrong).

You need to have a proper Date table related to your fact and a [YearMonth] column like 202508 (INT or TEXT). If your V1–V4 are columns you need to wrap them as measures.

Net Flow =
VAR Inflow  = COALESCE([V1],0) + COALESCE([V2],0)
VAR Outflow = COALESCE([V3],0) + COALESCE([V4],0)
RETURN Inflow - Outflow

You can create a disconnected table Anchor Period with one column [YearMonth] and let the user pick the extraction period :

Anchor Date =
VAR AnchorYM = SELECTEDVALUE('Anchor Period'[YearMonth])
RETURN
CALCULATE(
    MIN('Date'[Date]),
    ALL('Date'),
    'Date'[YearMonth] = AnchorYM
)

Or if you always want to anchor to the last period that has stock at time:

Anchor Date =
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        NOT ISBLANK( CALCULATE([Stock At Time]) )
    )
)

Then projected stock :

Projected Stock =
VAR tDate       = MAX('Date'[Date])
VAR anchorDate  = [Anchor Date]
VAR InitialStock =
    CALCULATE(
        [Stock At Time],
        ALL('Date'[Date]),
        'Date'[Date] = anchorDate
    )
VAR NetSinceAnchor =
    CALCULATE(
        [Net Flow],
        ALL('Date'[Date]),                               
        DATESBETWEEN('Date'[Date], anchorDate, tDate)   
    )
RETURN
IF(
    tDate < anchorDate,                                  
    BLANK(),
    InitialStock + NetSinceAnchor
)

[v-pnaroju-msft · Accepted solution]

Thankyou, @bhanu_gautam, @danextian, @Shahid12523 and @AmiraBedh for your responses.

Hi PBIDev01,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding of the scenario, please find attached a screenshot and a sample PBIX file which may assist in resolving the issue:

We hope the information provided proves helpful. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[PBIDev01 · Accepted solution]

Hello again,

Thanks so much for sharing your ideas, they're really helpful. I'm currently putting together a sample file to share with you, but honestly, I think we're already close to a solution based on what you've suggested.
Thank you.

[HarishKM · Accepted solution]

@PBIDev01  Hey,
Use this as your previous dax, I have created this.

ProjectedStock =
VAR CurrentPeriod = MAX(Calendar[Period])
VAR InitialStock = CALCULATE([V1]+[V2]+Stock_At_Time-[V3+V4], Calendar[Period] = 202508)
VAR ProjectedStockInitial = InitialStock

RETURN
SUMX(
FILTER(
ALL(Calendar[Period]),
Calendar[Period] <= CurrentPeriod
),
IF(
Calendar[Period] = MIN(Calendar[Period]),
ProjectedStockInitial,
[V1] + [V2] + [ProjectedStock] - [V3] - [V4]
)
)

Thanks

Harish M

Kindly give Kudos and accept it as solution if its solves your problem

[PBIDev01 · Accepted solution]

Hello again,

Thanks so much for sharing your ideas, they're really helpful. I'm currently putting together a sample file to share with you, but honestly, I think we're already close to a solution based on what you've suggested.
Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @bhanu_gautam, @danextian, @Shahid12523 and @AmiraBedh for your responses.

Hi PBIDev01,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding of the scenario, please find attached a screenshot and a sample PBIX file which may assist in resolving the issue:

We hope the information provided proves helpful. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[AmiraBedh · Accepted solution]

Hello !

I don't think that you need recursion here what I understood is that you need an anchored running balance. (please correct me if I am wrong).

You need to have a proper Date table related to your fact and a [YearMonth] column like 202508 (INT or TEXT). If your V1–V4 are columns you need to wrap them as measures.

Net Flow =
VAR Inflow  = COALESCE([V1],0) + COALESCE([V2],0)
VAR Outflow = COALESCE([V3],0) + COALESCE([V4],0)
RETURN Inflow - Outflow

You can create a disconnected table Anchor Period with one column [YearMonth] and let the user pick the extraction period :

Anchor Date =
VAR AnchorYM = SELECTEDVALUE('Anchor Period'[YearMonth])
RETURN
CALCULATE(
    MIN('Date'[Date]),
    ALL('Date'),
    'Date'[YearMonth] = AnchorYM
)

Or if you always want to anchor to the last period that has stock at time:

Anchor Date =
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        NOT ISBLANK( CALCULATE([Stock At Time]) )
    )
)

Then projected stock :

Projected Stock =
VAR tDate       = MAX('Date'[Date])
VAR anchorDate  = [Anchor Date]
VAR InitialStock =
    CALCULATE(
        [Stock At Time],
        ALL('Date'[Date]),
        'Date'[Date] = anchorDate
    )
VAR NetSinceAnchor =
    CALCULATE(
        [Net Flow],
        ALL('Date'[Date]),                               
        DATESBETWEEN('Date'[Date], anchorDate, tDate)   
    )
RETURN
IF(
    tDate < anchorDate,                                  
    BLANK(),
    InitialStock + NetSinceAnchor
)

[Shahid12523 · Accepted solution]

Use this

ProjectedStock =
VAR CurrPeriod = MAX(FactStock[Period])
VAR BasePeriod = 202508 // extraction period
VAR StartStock =
CALCULATE(MAX(FactStock[Stock_At_Time]), FactStock[Period] = BasePeriod)
VAR Movements =
SUMX (
FILTER ( FactStock, FactStock[Period] <= CurrPeriod && FactStock[Period] > BasePeriod ),
FactStock[V1] + FactStock[V2] - FactStock[V3] - FactStock[V4]
)
RETURN
StartStock + Movements

[danextian · Accepted solution]

It would be easier for us to come up with a solution if you just provided a sample pbix with confidential data removed.

[bhanu_gautam · Accepted solution]

@PBIDev01 First, calculate the ProjectedStock for the initial period (e.g., 202508) using a calculated column. This will serve as the base for subsequent calculations.

ProjectedStock_Initial = [V1] + [V2] + Stock_At_Time - ([V3] + [V4])

Use a calculated table to iterate over the periods and calculate the ProjectedStock for each subsequent period. This approach avoids circular dependencies by storing intermediate results in a separate table.

ProjectedStockTable =
VAR BasePeriod = 202508
VAR MaxPeriod = 202511
RETURN
GENERATE(
FILTER(
ADDCOLUMNS(
GENERATESERIES(BasePeriod, MaxPeriod, 1),
"ProjectedStock",
VAR CurrentPeriod = [Value]
VAR PreviousPeriodStock =
IF(CurrentPeriod = BasePeriod,
[V1] + [V2] + Stock_At_Time - ([V3] + [V4]),
CALCULATE(
MAX(ProjectedStockTable[ProjectedStock]),
ProjectedStockTable[Period] = CurrentPeriod - 1
)
)
RETURN
PreviousPeriodStock + [V1] + [V2] - ([V3] + [V4])
),
[Value] >= BasePeriod
),
ROW("Period", [Value])
)