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

DAX_Sum_of_Budget_if_Actual_Month_available

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

DAX_Sum_of_Budget_if_Actual_Month_available

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

Re: DAX Sum of Budget if Actual-Month available

Thank you for the quick reply! Smiley Happy

patrisack Frequent Visitor
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!

Highlighted
Community Support Team
Community Support Team

Re: DAX Sum of Budget if Actual-Month available

Hi @patrisack,

 

Try this one, please.

 

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.