cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX Sum of Budget if Actual-Month available

Hi!

I'm having issues with creating a measure for estimated end-of-month results - taking actuals up to a selected date and using forecast-data for the remainder of the month.

I've created an example of what I want to do in this Excel-file. I want to replicate the formula there to DAX so I can use it in my report. Hopefully someone in this

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: DAX Sum of Budget if Actual-Month available

Hi @patrisack,

Please check out the demo in the attachment.

```Column =
VAR currentDatum = [Datum]
RETURN
CALCULATE (
SUM ( Table1[Actuals] ),
FILTER (
'Table1',
Table1[Datum] <= currentDatum
&& Table1[Datum]
>= ( EOMONTH ( currentDatum, -1 ) + 1 )
)
)
+ CALCULATE (
SUM ( Table1[Forecast] ),
FILTER (
Table1,
Table1[Datum] > currentDatum
&& Table1[Datum] <= EOMONTH ( currentDatum, 0 )
)
)
```

Note: your data is random. The result of [Column] here is correct.

Best Regards,
Dale

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

## Re: DAX Sum of Budget if Actual-Month available

Hi @patrisack,

Please check out the demo in the attachment.

```Column =
VAR currentDatum = [Datum]
RETURN
CALCULATE (
SUM ( Table1[Actuals] ),
FILTER (
'Table1',
Table1[Datum] <= currentDatum
&& Table1[Datum]
>= ( EOMONTH ( currentDatum, -1 ) + 1 )
)
)
+ CALCULATE (
SUM ( Table1[Forecast] ),
FILTER (
Table1,
Table1[Datum] > currentDatum
&& Table1[Datum] <= EOMONTH ( currentDatum, 0 )
)
)
```

Note: your data is random. The result of [Column] here is correct.

Best Regards,
Dale

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

## Re: DAX Sum of Budget if Actual-Month available

Thank you for the quick reply!

Frequent Visitor

## Re: DAX Sum of Budget if Actual-Month available

@v-jiascu-msftAny chance you could also formulate the calculation as a measure instead of a calculated column?

Thank you!

Community Support Team

## Re: DAX Sum of Budget if Actual-Month available

Hi @patrisack,

```Measure =
CALCULATE (
SUM ( Table1[Actuals] ),
FILTER (
ALL ( Table1 ),
'Table1'[Datum] <= MIN ( 'Table1'[Datum] )
&& 'Table1'[Datum]
>= EOMONTH ( MIN ( 'Table1'[Datum] ), -1 ) + 1
)
)
+ CALCULATE (
SUM ( Table1[Forecast] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[Datum] > MIN ( 'Table1'[Datum] )
&& 'Table1'[Datum] <= EOMONTH ( MIN ( 'Table1'[Datum] ), 0 )
)
)
```

Best Regards,
Dale

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