In the AdjProvStock LM column , i would like to get the previous month AdjProvStock as a calculated column.
To get it as a measure, this formula is enough
AdjProvStockLM = CALCULATE(SUM(Funding[AdjProvStock]),PARALLELPERIOD(‘Date’[Date],-1,MONTH))
But how to get it as a calculated column, I Understand it can be done using earlier function, but how exactly should it be done.Fund.pbix (172.0 KB)
@Anu,
Here’s how I would do it using EARLIER. First add an index column to your table, then use this for your calculated column.
AdjProvStock LM =
VAR PrevDate =
CALCULATE(
MAX( Funding[Date] ),
FILTER(
ALL( Funding ),
Funding[Index] < EARLIER( Funding[Index] )
)
)
VAR Result =
CALCULATE(
MAX( Funding[AdjProvStock] ),
FILTER(
ALL( Funding ),
Funding[Date] = PrevDate
)
)
RETURN Result
@Greg has put together an awesome summary of all the common previous row value patterns here in the link below. It’s got an alternative approach using variables to capture the prior row context instead of EARLIER that is now the generally recommended approach:
I hope this is helpful. Full solution file attached below.
Could you please attach the the pbix also
Sure – solution file now attached above.
