DAX Sum of Budget if Actual-Month available

[Anonymous]

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!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

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

Thank you!

[v-jiascu-msft]

Hi @Anonymous,

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

[Anonymous]

I would like to apply a filter every month like 
Ie, For example if I select May month, It should display like this for me = 

Cumulative May=

Var April = CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]), new_shptransactionscurrentyears[new_postingdate].[monthNo] = 4)

Var May = CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]), new_shptransactionscurrentyears[new_postingdate].[monthNo] = 5)

return (April + May)



Help is greatly appreciated 

[Anonymous]

Thank you for the quick reply!