Forum Discussion
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 there are a ton of different options. I wanted to see if there was something that I need to do with my data set that is preventing this from being done.
Thank you
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 )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Sorry guys the Divide formula must be looking like this
Sorry again
Regards
8 Replies
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.
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
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.
Anonymous
I create a sample data.
Then you create measures to calculate last year value and YOY%
Anonymous , with a date calendar please find various ways to have YOY
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year)) Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date])) YTD QTY forced= var _max = today() return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max = date(year(today())-1,month(today()),day(today())) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max) YTD QTY forced= var _max = maxx('order',[Order date]) return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max1 =maxx('order',[Order date]) var _max = date(year(_max1)-1,month(_max1),day(_max1)) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max) //Only year vs Year, not a level below This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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 )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi V-lianl-msft
Just to add a little detail : you need to divide Max ('Table'[Sales]) / 100 to get the correct result
Amine
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry guys the Divide formula must be looking like this
Sorry again
Regards
