Forum Discussion

cosminc's avatar
cosminc
Post Partisan
7 years ago
Solved

Time Intelligence - YOY not working

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

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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's avatar
    cosminc
    Post Partisan
    7 years ago

    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