Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YoY Quick Measure - Date Issue

Hello,   I am trying to calculate Sales $ year over year change. I have my data by year however, the app will not allow me to add any data into the date field. I have read multiple articles and the...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Try measure like this:

    Measure =
VAR year =
    MAX ( 'Table'[year] )
VAR last_year_sale =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[sales], 1 ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[vendor] ), 'Table'[year] = year - 1 )
    )
RETURN
    DIVIDE ( MAX ( 'Table'[sales] ), last_year_sale )

    Sample .pbix 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • aj1973's avatar
    aj1973
    6 years ago

    Sorry guys the Divide formula must be looking like this

     

    Sorry again

    Regards

ryan_mayu's avatar
ryan_mayu
Super User
6 years ago

Anonymous 

 

It's better to create a measure (writing DAX by yourself). You need a datetime table and create the relationship between your table and datetime table. Then you can use dateadd to get the previous year's value.

 

It will be better if you share the screenshot or sample file.

 

  • aj1973's avatar
    aj1973
    Community Champion
    6 years ago

    Hi,

    The YoY measure is a Time Intelligent function, for that you need to create a Calendar table in order to use it proparly.

     

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello,

     

    ryan_mayu thank you for your help. My data is summarizing the Vendor # by Year. I can not have the date by month as it will be too many rows. I have an excel doc that caclculates YOY calculations and I am trying to the same in Power BI.

     

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      6 years ago

      Anonymous 

       

      I create a sample data.

      Then you create measures to calculate last year value and YOY%