Calculate a Rolling Sum by Fiscal Period

[Anonymous]

Hi!

I have a dataset that measures transaction quantities by product and business unit. I want to add a custom column to my report that shows the "Opening Quantity" for fiscal period x where the Opening Quantity is equal to my Cumulative Quantity of a specific product plus the sum of period quantities preceding period x. For example, for Period = 3 the Opening Quantity would be Cumulative Quantity + Period 1 Net Qty + Period 2 Net Qty. I'm fairly new to Power BI so I am struggling with writing a measure that can give me the expected result.

Conditions for the quantities to roll up include:

-Must be of the same business unit

-Must be of the same item number

-Must be of the same century and fiscal year

-Do not roll up if period is equal to or greater than the current period

See the below sample:

Business Unit	Item Number	Century	Fiscal Year	Period	Cumulative Quantity	Net Quantity	Opening Quantity
202066	51011000	20	20	1	1000	100	1000	@Period=1; Opening Qty = Cumulative Qty
202066	51011000	20	20	2	1000	200	1100	@Period=2; Opening Qty = Cumulative Qty + Period 1 Net Qty
202066	51011000	20	20	3	1000	300	1300	@Period=3; Opening = Cumulative + Period 1 Net + Period 2 Net
202066	51011000	20	20	4	1000	400	1600	@Period=4; Opening = Cumulative + Period 1 Net + Period 2 Net + Period 3 Net + Period 4 Net
202066	51011000	20	20	5	1000	500	2000	@Period=4; Similarly Σp_5 = Cumulative + p_1 + p_2 + … + p_5

[CNENFRNL · Accepted solution]

@Anonymous , you might try

Opening Qty = 
SUMX (
    DISTINCT ( Table1[Business Unit] ),
    SUMX (
        DISTINCT ( Table1[Item Number] ),
        VAR __p = MAX ( Table1[Period] )
        RETURN
            CALCULATE ( SUM ( Table1[Net Quantity] ), Table1[Period] < __p )
                + MAX ( Table1[Cumulative Quantity] )
    )
)

[Anonymous]

Thank you very much, your code worked exactly as intended!

[amitchandak]

@Anonymous 

Create a new table with Century Fiscal Year Period
Create a key in that table(Say Date) and fact table(You shared)

new columns
key = [Century]*10000 [Fiscal Year]*100 + [Period]
month Rank = RANKX(all('Date'),'Date'[key],,ASC,Dense)

New Measures
Cumm This Month = CALCULATE(sum('Table'[Cumulative Quantity]), FILTER(ALL('Date'),'Date'[month Rank]=max('Date'[month Rank]) ))

Net Quantity till last month = CALCULATE(sum('Table'[Net Quantity]), FILTER(ALL('Date'),'Date'[Fiscal Year]=max('Date'[Fiscal Year]) && 'Date'[month Rank] < Max('Date'[month Rank])))

Opening Quantity = [Cumm This Month] + [Net Quantity till last month ]

one more variation of new without year

Net Quantity till last month = CALCULATE(sum('Table'[Net Quantity]), FILTER(ALL('Date'), 'Date'[month Rank] < Max('Date'[month Rank])))