BUG: PowerBI January 2017 update broke TOTALMTD()

[sbstern]

Hi - It seems like the latest PowerBI update broke something in either TOTALMTD() function or perhaps an IF() function. The below pattern used to ensure that the MTD revenue total wasn't continued to dates in the future, but after the January update it stopped doing this. The chart now plateaus at the revenue for the current day and extends that out to the end of the month.

I'd been using the below pattern to limit the sum to the current month.

Cumulative MTD Revenue =
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Orders[invoice_date] ), ALL ( Orders ) ),
    CALCULATE (
        TOTALMTD (
            SUM ( Orders[actual_total_amount] ),
            'Date'[Date],
            FILTER (
                ALL ( 'Date'[Date] ),
                MONTH ( 'Date'[Date] ) = MONTH ( NOW () )
                    && YEAR ( 'Date'[Date] ) = YEAR ( NOW () )
            )
        )
    )
)

[v-haibl-msft]

@sbstern

What is the old version you used before? With your DAX formula, if the invoice date is always not later than today, you will always the Cumulative MTD Revenue until today without future date. Is there any problem here? Please refer to my screenshot below.

Best Regards,

Herbert

[sbstern]

Thanks.

I had been using PowerBI 2.41.4581.301 (November 2016) -- and each of the prior builds.

In our process, the invoice date can never be later than today so that was what the formula was designed around. But it was iterating the MTD cumulative total over the remainder of the month (so if we were at $100,000 on January 15, it would still show $100,000 for Jan 16, 17, 18...31). The formula had worked fine before the January 2017 PowerBI update.

I was able to come up with a fix, but it still leaves me confused why the prior formula stopped working. Here's the fix:

Cumulative MTD Revenue = 
IF (
     max('Date'[Date])
        < today(),
    CALCULATE (
        TOTALMTD (
            SUM ( Orders[actual_total_amount] ),
            'Date'[Date],
            FILTER (
                ALL ( 'Date'[Date] ),
                MONTH ( 'Date'[Date] ) = MONTH ( NOW () )
                    && YEAR ( 'Date'[Date] ) = YEAR ( NOW () )
            )
        )
    )
)

[v-haibl-msft]

@sbstern

Following measure expression will return the MAX date in Orders table. If there are future days in the invoice_date column (e.g. 1/27/2017), it will return 1/27/2017 but not today.

CALCULATE ( MAX ( Orders[invoice_date] ), ALL ( Orders ) )

So your original DAX measure will always return the cumulative MTD revenue until the MAX invoice_date of this month. The Nov 2016 version of PBI Desktop return the same result as below. Your updated DAX expression should be meet your requirement.

Best Regards,

Herbert