cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Julien_F
Helper I
Helper I

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! 
 
😕
1 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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

17 REPLIES 17
amitchandak
Super User
Super User

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,""))

 

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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

@Julien_F remove this

 

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

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

@amitchandak 

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

@Julien_F 

 

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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

@Julien_F that sounds like plan. cheers!!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

btw I solved the prob by using : 

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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

 

PBI_ERROR.PNG

parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

 

image.png

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!