cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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

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],
)
)```

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],
)
,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
Anonymous
Not applicable

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],
)
)```

3 REPLIES 3
Community Support

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

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.
Anonymous
Not applicable

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],
)
)```

Community Support

HI, @Anonymous

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors