YoY analysis

[Anonymous]

Hello all,

 is there a way to get YoY comparison using weeks as referrence?

[v-alq-msft · Accepted solution]

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.

[Anonymous]

Hello Allan,

 Tks a lot! It solved my problem.

Kind rgds!

[amitchandak]

@Anonymous , I have blog on how to deal with week.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

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.

[Anonymous]

Hello, 

 Tks a lot. It will be really usefull!

Rgds!!

[parry2k]

@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/38490

[Anonymous]

Hello 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.