DAX Sum of Budget if Actual-Month available

[Anonymous]

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?

That would be really helpful!

Cheers,

Simon

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

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