Forum Discussion
Helper IIPrevious available month data comparison
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
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 ) )
2 Replies
- Fowmy
Super User
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 ) )
