Time Intelligence - YOY not working

[cosminc]

Hi

i use a calendar but with date = first date of each month (12 rows for each year) in relationship (Year-Month) with a  data base like this:

Salesman   Sales    YM                      Date                         Year               MonthName

A                 10        201901               01/01/2019             2019                     January

B                  15        201902               01/02/2019              2019                   February

C                 5         201801                01/01/2018             2018                   January

C                 5         201802                01/02/2018             2018                    February

and i have an expression which is no more working (when the calendar was day by day full year it was ok but i need in this form to reduce optimize the report)

YOY =

DIVIDE((CALCULATE(SUM(Source[Sales]))-CALCULATE(SUM(Source[Sales]),DATEADD('Calendar'[Date],-1,YEAR)))

,(CALCULATE(SUM(Source[Sales]),DATEADD('Calendar'[Date],-1,YEAR))),BLANK())

is put in a table like this:

Thanks,

Cosmin

[v-yulgu-msft · Accepted solution]

Hi @cosminc ,

Please modify the formula as below:

YOY =
VAR TY_sales =
    SUM ( Source[Sales] )
VAR LY_sales =
    CALCULATE (
        SUM ( Source[Sales] ),
        FILTER (
            ALLSELECTED ( Source ),
            Source[Year]
                = SELECTEDVALUE ( Source[Year] ) - 1
                && Source[MonthName] = SELECTEDVALUE ( Source[MonthName] )
        )
    )
RETURN
    DIVIDE ( TY_sales - LY_sales, LY_sales )

Best regards,

Yuliana Gu

[cosminc]

Thanks,

it works.

Cosmin

[cosminc]

in addition i need if i filter salesman to work properly

previous expression with full calendar eas working only  if salesman was not filtered or if that salesman had data for each month

thanks,

Cosmin