Recursive Measure Help

[Anonymous]

Hi All,

Need help with one scenario to Recursively Calculate Values. Below are sample values. These are measures in my Model.

Metrics	7/1/2020	7/8/2020	7/15/2020	7/22/2020	7/29/2020	8/5/2020	8/12/2020
Opening Bal	1357	0	0	0	0	0	0
InQualityInsp	1480	0	0	0	0	0	0
TotalDquantity	153	105	741	121	69	304	576
TotalSquantity	0	296	0	0	0	0	0

My Requirement is to create two new Measures as below. As my inputs are measures so primarily need Measures. If not possible, can try for Columns.

1. Opening Inventory = If Week1 then Opening Bal else Closing Inventory (Last Week) + InQualityInsp (Last Week)
2. Closing Inventory = Opening Inventory + TotalSquantity - TotalDquantity

Expected Outcome:

	7/1/2020	7/8/2020	7/15/2020	7/22/2020	7/29/2020	8/5/2020	8/12/2020
Opening Inventory	1357	2684	2875	2134	2013	1944	1640
Closing Inventory	1204	2875	2134	2013	1944	1640	1064

As Opening Inventory and Closing Inventory measures are interdependent, so getting Circular Dependency error.

Sample Files at https://www.dropbox.com/sh/d8jpiwki28dk1ef/AADGOWyxp8yzJ4XY5m5XXZ-Da?dl=0 

Thanks

Ankit J

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following 2 measures:

Opening Inventory =
CALCULATE (
    SUM ( Sheet3[Value] );
    FILTER (
        ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
        Sheet3[WeekDate] <= SELECTEDVALUE ( Sheet3[WeekDate] )
            && Sheet3[Metrics] IN { "Opening Bal" }
    )
)
    + CALCULATE (
        SUM ( Sheet3[Value] );
        FILTER (
            ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
            Sheet3[WeekDate] < SELECTEDVALUE ( Sheet3[WeekDate] )
                && Sheet3[Metrics] IN { "InQualityInsp"; "TotalsupplyQuantity" }
        )
    )
    - CALCULATE (
        SUM ( Sheet3[Value] );
        FILTER (
            ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
            Sheet3[WeekDate] < SELECTEDVALUE ( Sheet3[WeekDate] )
                && Sheet3[Metrics] = "TotalDemandquantity"
        )
    )

Closing inventory =
CALCULATE (
    SUM ( Sheet3[Value] );
    FILTER (
        ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
        Sheet3[WeekDate] <= SELECTEDVALUE ( Sheet3[WeekDate] )
            && Sheet3[Metrics] IN { "Opening Bal"; "TotalsupplyQuantity" }
    )
)
    + CALCULATE (
        SUM ( Sheet3[Value] );
        FILTER (
            ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
            Sheet3[WeekDate] < SELECTEDVALUE ( Sheet3[WeekDate] )
                && Sheet3[Metrics] IN { "InQualityInsp" }
        )
    )
    - CALCULATE (
        SUM ( Sheet3[Value] );
        FILTER (
            ALL ( Sheet3[Metrics]; Sheet3[WeekDate] );
            Sheet3[WeekDate] <= SELECTEDVALUE ( Sheet3[WeekDate] )
                && Sheet3[Metrics] = "TotalDemandquantity"
        )
    )

Check PBIX file attach.