cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trojii Frequent Visitor
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:

2017-04-27_09h20_58.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-ljerr-msft Super Contributor
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? Smiley Happy

 

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
v-ljerr-msft Super Contributor
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? Smiley Happy

 

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

trojii Frequent Visitor
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.

2017-04-28_11h03_56.png

 

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