Forum Discussion
YoY % Change
Hi Power BI user,
I have appilied below query for YoY % calculation and receing error.
Could you pelase check and help me to rectify error. as below.
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR PrviousYear = CurrentYear-1
VAR CurrentYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]), 'Calendar'[Year]=CurrentYear)
VAR PreviousYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]),'Calendar'[year]=PreviousYear)
RETURN
DIVIDE(CurrentYearSalesDocument-PreviousYearSalesDocument,PreviousYearSalesDocument)
Hi ktkpatra ,
I noticed a typo in your formula: Prvious Year.
If you just misspelled it in a post, but you spelled it correctly in Power BI Desktop. I think you should be able to try my YOY measure.
YOY = VAR _CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR _PreviousYear = _CurrentYear-1 VAR _CurrentYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]), 'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_CurrentYear) VAR _PreviousYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]),'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_PreviousYear) RETURN DIVIDE(_CurrentYearSalesDocument-_PreviousYearSalesDocument,_PreviousYearSalesDocument )Note that there's no relationship.
If you need to have a relational presence, then you can modify the measure to:
YOY 2 = VAR _CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR _PreviousYear = _CurrentYear-1 VAR _CurrentYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]), 'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_CurrentYear) VAR _PreviousYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]),FILTER(ALL(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT),[Year]=_PreviousYear)) RETURN DIVIDE(_CurrentYearSalesDocument-_PreviousYearSalesDocument,_PreviousYearSalesDocument )You can download my attachment for more details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hello! I suggest creating an explicit measure for your DISTINCTCOUNT (for now, we'll call it [CountOfSalesDocument]), and having a year filter in the filter pane, then you can do below:
CountOfSalesDocument YoY% =VAR __PREV_YEAR = CALCULATE([CountOfSalesDocument], DATEADD('Date'[Date], -1, YEAR))RETURNDIVIDE([Extended Amount] - __PREV_YEAR, __PREV_YEAR)Hi ktkpatra ,
I noticed a typo in your formula: Prvious Year.
If you just misspelled it in a post, but you spelled it correctly in Power BI Desktop. I think you should be able to try my YOY measure.
YOY = VAR _CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR _PreviousYear = _CurrentYear-1 VAR _CurrentYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]), 'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_CurrentYear) VAR _PreviousYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]),'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_PreviousYear) RETURN DIVIDE(_CurrentYearSalesDocument-_PreviousYearSalesDocument,_PreviousYearSalesDocument )Note that there's no relationship.
If you need to have a relational presence, then you can modify the measure to:
YOY 2 = VAR _CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR _PreviousYear = _CurrentYear-1 VAR _CurrentYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]), 'ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT'[Year]=_CurrentYear) VAR _PreviousYearSalesDocument = CALCULATE(DISTINCTCOUNT(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT[Sales Document]),FILTER(ALL(ZCD_OTC_CV_OPEN_CLOSED_ORDERS_DCT),[Year]=_PreviousYear)) RETURN DIVIDE(_CurrentYearSalesDocument-_PreviousYearSalesDocument,_PreviousYearSalesDocument )You can download my attachment for more details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
