DAX: Rolling calculation using 2 inputs from different tables where some values are missing

[Anonymous]

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 difficulty because I'd like to filter out months where one of the input values is missing. 

My simplified data model looks like this:

Keys are: Location and Month

Data is ValueA in TableA and ValueB in TableB

I am calculating ValueC = ValueA / ValueB

Here is the data shown together in a single table. As you can see, some months (March 2018 - Sept 2018) are missing ValueA. As expected, ValueC is blank for those months (because C = A/B)

However, I'm having difficulty calculating ValueC for a rolling 12 months. When I calculate ValueC, I'd like to exclude any months where either ValueA or ValueB is missing. In the example row below, it's dividing the ValueA for October 2018 by the ValueB for March 2018 through October 2018.

I am trying to exclude March 2018 through September 2018, since I'm missing an input value for those months. 

Any ideas about how I would accomplish this?

This is the DAX I have so far:

ValueC is a simplie division:

ValueC = 
CALCULATE(
    DIVIDE(
        SUM(TableA[ValueA]),
        SUM(TableB[ValueB]),
        BLANK()
    )
)

Rolling 12 Mo Sum of ValueA

ValueA (Last 12 Mo) = 
CALCULATE(
    SUM(TableA[ValueA])
    ,DATESBETWEEN(
        Months[Month],
        DATEADD(LASTDATE(Months[Month]),-12,MONTH),
        DATEADD(LASTDATE(Months[Month]),0,MONTH)
    )
)

Rolling 12 Mo sum of ValueB

ValueB (Last 12 Mo) = 
CALCULATE(
    SUM(TableB[ValueB])*(SUM(TableA[ValueA])+0)/(SUM(TableA[ValueA])+0)
    ,DATESBETWEEN(
        Months[Month],
        DATEADD(LASTDATE(Months[Month]),-12,MONTH),
        DATEADD(LASTDATE(Months[Month]),0,MONTH)
    )
    ,FILTER(ALLSELECTED(Months),SUM(TableA[ValueA])>0)
)

(ValueC Rolling 12 Mo Sum)= (ValueA Rolling 12 Mo Sum) / (Value B Rolling 12 Mo Sum)

ValueC (Last 12 Mo) = 
CALCULATE(
    DIVIDE(
        [ValueA (Last 12 Mo)],
        [ValueB (Last 12 Mo)],
        BLANK()
    )
)

[v-lili6-msft]

hi, @Anonymous 

Do you mean that if between the current 12 months, if there one month that doesn't have both ValueA and ValueB at the same month, this month will be excluded?

Best Regards,

Lin

[Anonymous · Accepted solution]

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)
    )
)

[v-lili6-msft]

HI, @Anonymous 

It's pleasant that your problem has been solved, could you please mark the reply as Answered?

Best Regards,

Lin