Forum Discussion

Julien_F's avatar
Julien_F
Icon for Helper I rankHelper I
7 years ago
Solved

QUICk MEASURE FAILED

Hello, 

 

WHat is wrong here 

 

Gross YoY% =
IF(
    ISFILTERED('BNP_FINANCIAL_NIELSEN_OUTPUT'[PUB_DATE]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_YEAR =
        CALCULATE(
            SUM('BNP_FINANCIAL_NIELSEN_OUTPUT'[Gross]),
            DATEADD('BNP_FINANCIAL_NIELSEN_OUTPUT'[PUB_DATE].[Date], -1,YEAR)
        )
    RETURN
        DIVIDE(
            SUM('BNP_FINANCIAL_NIELSEN_OUTPUT'[Gross]) - __PREV_YEAR,
            __PREV_YEAR
        )
)
 
It said year syntax is not correct! 
 
:/
Need Help
  • amitchandak's avatar
    amitchandak
    7 years ago

    In case you need this year vs Last year

    On the Fly Change % = 
Var _last_year= year(max('Date'[Date Filer]))-1
Var   _This_year=year(max('Date'[Date Filer]))

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),YEAR(Sales[Sales Date])=_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),YEAR(Sales[Sales Date])=_This_year)
return
(_This_year_val-_last_year_val)/_last_year_val*100

     

17 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Julien_F is data type for your pub_date is date?

     

    Also this kind of time intelligence function are better to be used wiht date dimension. Do you have date dimension in your model?

    • Julien_F's avatar
      Julien_F
      Icon for Helper I rankHelper I
      7 years ago

      parry2k Yeah it is a date...

      It was lol

       

      It is very strange because I have a lot of strange PBI behavior: 

      The dax I posted below is working like 5 sec then I have the error. 

       

      Look the format is date but I do not have the  "logo" who said the clonum format ,  :/

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    7 years ago

    I think it because of

     

    CALCULATE(
            SUM('BNP_FINANCIAL_NIELSEN_OUTPUT'[Gross]),
            DATEADD('BNP_FINANCIAL_NIELSEN_OUTPUT'[PUB_DATE].[Date], -1,YEAR)
        )

    The second parameter should be a filter  example

    2nd last year = CALCULATE(sum(Sales[Sales]),OrderTime[Order Year]= CONCATENATE( Year(NOW())-2,""))

     

     

     

    • Julien_F's avatar
      Julien_F
      Icon for Helper I rankHelper I
      7 years ago

      amitchandak parry2k 

       

      Maybe...

       

      But I used quick measure so does it mean PBI did it wrong? 

       

      parry2k  I created a DIM_DATE to be sure but still have the same prob 

       

      Gross YoY% =
      IF(
          ISFILTERED('Table'[Date]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __PREV_YEAR =
              CALCULATE(
                  SUM('BNP_FINANCIAL_NIELSEN_OUTPUT'[Gross]),
                  DATEADD('Table'[Date].[Date], -1, YEAR)
              )
          RETURN
              DIVIDE(
                  SUM('BNP_FINANCIAL_NIELSEN_OUTPUT'[Gross]) - __PREV_YEAR,
                  __PREV_YEAR
              )
      )
       
      In red this what PBI said it is wrong ;)
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Julien_F remove this

         

        change dateadd as following instead of Table[Date].[Date]

         

            DATEADD('Table'[Date], -1, YEAR)