TOTALYTD with DirectQuery

[amitnarkar3]

Hi All,

Could you suggest an aquivalent for TOTALYTD() function in DirectQuery mode?

This should work with Date Slicer selection.

Thanks,

Amit

[mattbrice]

CALCULATE (
    SUM ( Table[Column] ),
    FILTER (
        ALL ( Calendar ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && YEAR ( 'Calendar'[Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) )
    )
)

[amitnarkar3]

this also works only when I enable unrestricted option in power bi desktop. will it still work on power bi portal or azure portal?

Do we need to enable same thing in that case and from where I can do it?

[Eric_Zhang]

@amitnarkar3

You just publish and don't have to change anything in Power BI Service.

[Eric_Zhang · Accepted solution]

@amitnarkar3

You can try to click "File"->"Options and settings"->"Options"->"DirectQuery"->check "allow unstricted measures" to enable TOTALYTD.

Do mind the that using the unstricted measures would affect performance in DirectQuery mode.

[amitnarkar3]

thanks I could use it after the option is enabled.

[amitnarkar3]

@Eric_Zhang

I need to publish this on Azure portal, will it still work. Do I need to also enable this option anywhere else?