Forum Discussion

maltamar's avatar
maltamar
Frequent Visitor
2 years ago

Calculation Group For Multiple measures

Hey Guys,

 

I have the following situation: 
I need to calculate the YTD, YTD (Last year),YoY Diff, YoY% for 23 different measures already listed in a matrix, I created a calculation group with 2 calculated items YTD and YTDLY, This works perfectly when adding the CGroup to the matrix as a column

BUT
when i create another calculation item for the YoY Diff and YoY% the matrix breaks and it gives me the error "Cannot convert "#0000000" of type text to number/date"
Here's the measure for the YoY%

Delta YTD Vs SPLY = 
VAR _YTD = CALCULATE(SELECTEDMEASURE(),CALENDAR_BY_DAY[IS_YTD] = 1)
VAR _SPLY = CALCULATE(SELECTEDMEASURE(),CALENDAR_BY_DAY[IS_YTD_LASTYEAR] = 1) 
    RETURN
        DIVIDE(_YTD,_SPLY,0)-1
I have a calendar table with the columns IS_YTD and IS_YTD_LY added, but same thing happens when using SAMEPERIODLASTYEAR or TOTALYTD


I made sure all my measures are type numbers which is why the first two calculation items work.

 

Anyways, is what i'm trying to do even possible? What am I doing wrong? Does this only works for one measure?

 

Appreciate some help here, thanks.

Need Help

1 Reply

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Hello! Calculation Groups can have many measures. Also, make sure your date table is marked as a date table. Try below:

    YoY% = 

    VAR __PREV_YEAR = CALCULATE(SELECTEDMEASURE(), DATEADD('Date'[Date], -1, YEAR))
    RETURN
        DIVIDE(SELECTEDMEASURE() - __PREV_YEAR, __PREV_YEAR)
     
    YoY = 
    SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR, 'Date'[Date])