Forum Discussion
YoY analysis
Hello all,
is there a way to get YoY comparison using weeks as referrence?
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create calculated columns and measures as below.
Calculated column: Year = YEAR('Table'[Date]) Weeknum = WEEKNUM('Table'[Date]) Measure: Result = CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Year]=SELECTEDVALUE('Table'[Year])-1&& 'Table'[Weeknum]=SELECTEDVALUE('Table'[Weeknum]) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous everything is possible with time intelligence DAX functions. provide more context.
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hello Parry,
Tks for helping. I was trying to have a measure using the weeks as referrence, but I was using the formula "sameperiodlastyear", and this formula calculates the range of date from last year, using as referrence the range of dates from the current year. I just needed to get the total of the same week of last year.
With the Allan's recommendation I was able to get the info using weeks as referrence instead of range of dates.
Thank you very much for your support and the links you shared. I started to use Power BI this month and a few things are new to me yet.
Anonymous , I have blog on how to deal with week.
Plus these formulae to be referred for year week till date
YTD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year]))) LYD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year])-1 )) WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday]))) LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday]))) LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52) && 'Date'[Weekday] <=max('Date'[Weekday]))) LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=(max('Date'[Year]) -1) && 'Date'[Week Number]=(max('Date'[Week Number])) && 'Date'[Weekday] <=max('Date'[Weekday])))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
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create calculated columns and measures as below.
Calculated column: Year = YEAR('Table'[Date]) Weeknum = WEEKNUM('Table'[Date]) Measure: Result = CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Year]=SELECTEDVALUE('Table'[Year])-1&& 'Table'[Weeknum]=SELECTEDVALUE('Table'[Weeknum]) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
