Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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

 

 

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

 

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.