QUICk MEASURE FAILED

[Julien_F]

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! 

😕

[amitchandak]

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]

@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]

@Julien_F remove this

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

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

[parry2k]

@Julien_F although you should use DIM_Date[Date] instead of Table[Date], assuming you have created relationship between DIM_Date and your table on date column.

Also mark your DIM_Date as date table, which you can do it on modelling tab.

[parry2k]

@Julien_F are you open to share pbix file? Remove any sensitive information before sharing.

[amitchandak · Accepted solution]

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

[Julien_F]

@amitchandak 

Thx you make my day haha ( didn't know that one ! ) 

[v-chuncz-msft]

@Julien_F 

By the way, you may help accept solution. Your contribution is highly appreciated.

[amitchandak]

In case you need 365 days before (All data < date-365, All Data)

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

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

[Julien_F]

@parry2k 

I cannot 😕 

Data are from Nielsen and clients pay for that BUT I can maybe create a fake data set ( lookalike) and try the Dax code again and then share it with you 😉 

[parry2k]

@Julien_F that sounds like plan. cheers!!

[Julien_F]

@parry2k 

btw I solved the prob by using : 

YOY = CALCULATE([GROSS_SUM],SAMEPERIODLASTYEAR(DIM_DATE[Date].[Date]))

[parry2k]

@Julien_F That surely make sense but I'm still interested to find out why the original measure was not working. Anyhow, it's upto you if you still want to share sample file and try to find out why original measure not worked, or you can carry on with this new measure and close this post here. Your call. 

[Julien_F]

@parry2k 

Yes me too but I have to work for my client and here it is already 7:30 pm, 

Bu I will do it for sure! 

Thx a lot for averything, 

J.

[Julien_F]

@parry2k 

I did! An changed table into DIM_DATE too, 

Same errror ... I aslo tried and olad one by restarting my computer but with no success 😕

[parry2k]

@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]

@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 ,  😕