Measure using another measure minus previous date.

[ROG]

Hi all,

I would like to add another column the table below that show me the 'Opening Base'.
I know that the  'Opening Base' is sum(active_customers) from fsubscriber_table on the last day of the previous month.
Basically on the table below, the Active Customers value 733,719 from the 31st of Jan 2022 should be the value of the 'Opening Base' on the 1st of Feb 2022.
I'm struggling to get this logic in a measure. 

Many thanks.

[vapid128]

add colnum:

YYMM = YEAR([date])*12+MONTH([date])

Measure 2 =

var _lastDate = CALCULATE(MAX([date]),FILTER(ALL(table22), table22[YYMM]= MAX(table22[YYMM])-1))

return CALCULATE([Measure],FILTER(ALL(table22),table22[date]=_lastDate))

Measure 3 =

IF(DAY( MIN(table22[date]))=1,

[Measure 2],

[Measure]

)

[ROG · Accepted solution]

Hey, that works! Thank you.