cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jtgriffith Frequent Visitor
Frequent Visitor

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

 

 

 

data_model.png

 

 

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)

dataTable1.png

 

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. 

 

dataTable2.png

 

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

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jtgriffith Frequent Visitor
Frequent Visitor

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

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

 

3 REPLIES 3
Community Support Team
Community Support Team

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

hi, @jtgriffith 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jtgriffith Frequent Visitor
Frequent Visitor

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

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

 

Community Support Team
Community Support Team

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

HI, @jtgriffith 

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.