Previous available month data comparison

[Malsk1_1]

Hi,

In my basic model, i have a MoM table, which is linked via the baseline to the Connected Date table.

See link Previous available month data 

The importance of this issue is that there is no September data, so we move from Aug 21 and then onto Oct 21, however at some point the September data will be made available. During this interim phase i need to compare the Oct 21 data to Aug 21 (as if i compare versus Sept it will always be Oct 21 data versus 0 as there is no data for Sept.)

How can i make the measure dyanmic such that if the previous month end is not avaiable then take the value from the latest previous month end?

I have tried the following and the end goal is as shown - the data in the shred link is different but the concept is the same

MeasurePower BI End Goal

As you can see the change displayed for the baseline October 21 shows an increase as it is comparing against Sept data which doesnt exist, in this case i would like to compare with August data - which should then show no change as the cost is the same.

For the previous month i am using the calculate(MoM[cost],PREVIOUSMONTH('Connected Date'[Date])

Any help would be appreciated.

Thanks in advance.

[Malsk1_1]

@Fowmy thank you - that worked prefectly!!

[Fowmy · Accepted solution]

@Malsk1_1 

Create the following measure. I attached the file.

Previous Month Cost = 
VAR LastCostDate = MAX(MoM[baseline])
VAR LastData = CALCULATE( EOMONTH( MAX(MoM[baseline]) , 0 )  , Dates[Date] < LastCostDate )
VAR DateFilter = DATESINPERIOD(Dates[Date],LastData, -1 , MONTH ) 
RETURN
IF( HASONEVALUE(Dates[Year Month]),  CALCULATE( [Month Cost],   DateFilter  ) )