Forum Discussion

admin11's avatar
admin11
Memorable Member
5 years ago
Solved

Measure return correct value , but get syntax shown error sign , how to clear it ?

Hi All

 

Below measure working fine :-

_LYTD_REV =
var _max = date(year(today())-1,month(today()),day(today()))
return
TOTALYTD(('GL'[AMOUNT_REV]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


I have decide to add one more condition to above measure , SOURCE=TS :-
_LYTD_REV TDS =
var _max = date(year(today())-1,month(today()),day(today()))
return
TOTALYTD(('GL'[AMOUNT_REV]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max,
FILTER( GL, [SOURCE] = "TS" ))
 
Above measure return correct result , but it shown error sign , see below image :-

May i know how to clear the error ?

 

Below my PBI file :-

https://www.dropbox.com/s/8fzg8stsqhqensy/PBT_V2021_392%20TI_SI_GL%20REV%20measure%20shown%20error%20sign%20how%20to%20clear.pbix?dl=0

Paul Yeo

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, admin11 

     

    Have your problem been solved? I checked your file, It seems that the third parameter in TOTALYTD can only write one filter, and the second will become the fourth parameter by default, causing errors, but the actual calculation will be included.

    You can easily use calculate to remove red error.

    Like this:

     

    _LYTD_REV TDS =
VAR _max =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        TOTALYTD (
            ( 'GL'[AMOUNT_REV] ),
            DATEADD ( 'Date'[Date], -1, YEAR ),
            'Date'[Date] <= _max
        ),
        FILTER ( GL, [SOURCE] = "TS" )
    )

     

     

    Best Regards

    Janey Guo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, admin11 

     

    Have your problem been solved? I checked your file, It seems that the third parameter in TOTALYTD can only write one filter, and the second will become the fourth parameter by default, causing errors, but the actual calculation will be included.

    You can easily use calculate to remove red error.

    Like this:

     

    _LYTD_REV TDS =
VAR _max =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        TOTALYTD (
            ( 'GL'[AMOUNT_REV] ),
            DATEADD ( 'Date'[Date], -1, YEAR ),
            'Date'[Date] <= _max
        ),
        FILTER ( GL, [SOURCE] = "TS" )
    )

     

     

    Best Regards

    Janey Guo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I'm not 100% sure but you can clean it up a little by removing the Filter function. This shouldn't be needed as the TOTALYTD function is already expecting a filter here. 

     

     

     

    LYTD_REV TDS =
VAR _max =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    TOTALYTD (
        ( 'GL'[AMOUNT_REV] ),
        DATEADD ( 'Date'[Date], -1, YEAR ),
        'Date'[Date] <= _max,
        GL[SOURCE] = "TS"
    )