Forum Discussion
DAX: Rolling calculation using 2 inputs from different tables where some values are missing
I'm trying to create a rolling 12 month calculation as a measure. The calculation is a simple division: take a value from TableA and divide it by the corresponding value in TableB. I'm having difficu...
That is correct.
- If ValueA and ValueB and both present for the all of the last 12 months, then use all 12 months of data.
- If ValueA is present for 9/12 months and ValueB is present for 12/12 months, then use only those 9 months of data that have both values.
I think this may work for me:
ValueA = SUM(TableA[ValueA])
ValueB = SUM(TableB[ValueB])
ValueB (where ValueA not null) = CALCULATE( [ValueB], FILTER(Months,[ValueA]>0) )ValueC (Last 12 Months) = CALCULATE( DIVIDE( [ValueA], [ValueB (where ValueA not null)], BLANK() ) ,DATESBETWEEN( Months[month], DATEADD(LASTDATE(Months[month]),-12,MONTH), DATEADD(LASTDATE(Months[month]),-1,MONTH) ) )
That is correct.
- If ValueA and ValueB and both present for the all of the last 12 months, then use all 12 months of data.
- If ValueA is present for 9/12 months and ValueB is present for 12/12 months, then use only those 9 months of data that have both values.
I think this may work for me:
ValueA = SUM(TableA[ValueA])
ValueB = SUM(TableB[ValueB])
ValueB (where ValueA not null) =
CALCULATE(
[ValueB],
FILTER(Months,[ValueA]>0)
)ValueC (Last 12 Months) =
CALCULATE(
DIVIDE(
[ValueA],
[ValueB (where ValueA not null)],
BLANK()
)
,DATESBETWEEN(
Months[month],
DATEADD(LASTDATE(Months[month]),-12,MONTH),
DATEADD(LASTDATE(Months[month]),-1,MONTH)
)
)
HI, Anonymous
It's pleasant that your problem has been solved, could you please mark the reply as Answered?
Best Regards,
Lin