Direct Query - Running Sum/Total

[jersey417]

All,

I previously had a running sum query that worked perfectly. However, I migrated my data to a DirectQuery and it's not longer working due to the limited DAX functionality.

Initial Query before migrating to direct query.

Running Total =
IF (
    Report_Monthly[Date] <= TODAY (),
    CALCULATE (
        SUM ( Report_Monthly[Produced_Qty] ),
        ALLEXCEPT ( Report_Monthly, Report_Monthly[Work_Center] ),
        Report_Monthly[Date] <= EARLIER ( Report_Monthly[Date] )
    )
)

When I tired to use this same DAX function when switching over to a DirectQuery the following error displays.  

Based on other posts I assumed the following would have worked.

Running Total =
IF (
    Report_Monthly[Date] <= TODAY (),
CALCULATE (
    SUM ( Report_Monthly[Produced_Qty] ),
    FILTER (
        ALLEXCEPT ( Report_Monthly, Report_Monthly[Work_Center]),
        Report_Monthly[Date] <= MAX ( Report_Monthly[Date] ))
    )
)

However, I'm getting the following error "Function 'Calculate' is not allowed as part of calculated column DAX expression in DirectQuery models." I've  gone into options and selected "Allow unrestricted measures in DriectQuery mode."

[v-huizhn-msft · Accepted solution]

Hi @jersey417,

From your formula, you created a calculated column rather than a measure. In the case you posted, they create a measure rather than a calculated column. You get the error caused by DAX Formula Compatibility limitation, please review more details in the blog: DAX Formula Compatibility in DirectQuery Mode. 

Please create a measure using the formula below and check if it works fine.

Running Total =
IF (
    FIRSTNONBLANK ( Report_Monthly[Date], Report_Monthly[Date] ) <= TODAY (),
    CALCULATE (
        SUM ( Report_Monthly[Produced_Qty] ),
        FILTER (
            ALLEXCEPT ( Report_Monthly, Report_Monthly[Work_Center] ),
            Report_Monthly[Date] <= MAX ( Report_Monthly[Date] )
        )
    )
)

Best Regards,
Angelia