cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX Sum of Budget if Actual-Month available

Hi dear community,

I know, my question is similar to that one. However, unfortunately, it did not help resolving my problem.

I got following situation:

What I want to achieve:

Sum of Budget until month where actuals are available. I achieved showing blanks. However, as you can see in the screenshot, the total sum still represents 12 months.

My DAX:

Basically, the two measures come from the same table and column; once marked as budget and once as actual.

```Active Customers :=
CALCULATE (
SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] );
FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" );
dim_type[dty_type] = "Actual"
)```
```Active Customers Budget Blank :=
IF (
ISBLANK ( [Active Customers] );
BLANK ();
CALCULATE (
SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] );
FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" );
dim_type[dty_type] = "Budget"
)
)```

Do you know any DAX-Syntax for that?

Cheers,

Simon

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super Contributor

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

Hi @trojii,

Could you try the formula below to see if it works in your scenario?

```Active Customers Budget Blank :=
IF (
ISBLANK ( [Active Customers] );
BLANK ();
CALCULATE (
SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] );
FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" );
FILTER ( 'Table_With_Actual-Month_Column'; [Active Customers] <> BLANK () );
dim_type[dty_type] = "Budget"
)
)
```

Note: Replace 'Table_With_Actual-Month_Column' with your real table name that has the "Actual-Month" column.

Regards

2 REPLIES 2
Highlighted
Super Contributor

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

Hi @trojii,

Could you try the formula below to see if it works in your scenario?

```Active Customers Budget Blank :=
IF (
ISBLANK ( [Active Customers] );
BLANK ();
CALCULATE (
SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] );
FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" );
FILTER ( 'Table_With_Actual-Month_Column'; [Active Customers] <> BLANK () );
dim_type[dty_type] = "Budget"
)
)
```

Note: Replace 'Table_With_Actual-Month_Column' with your real table name that has the "Actual-Month" column.

Regards

Frequent Visitor

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

Awesome, that worked!

Thank you so much! That should help me moving on developing my tabular model.

This is the final query modified accordingly to your query:

```IF (
ISBLANK ( [Active Customers] );
BLANK ();
CALCULATE (
SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] );
FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" );
FILTER ( Datum ; [Active Customers] <> BLANK () );
dim_type[dty_type] = "Budget"
)
)```

Best Regards