cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
espinale
Frequent Visitor

Measure of YoY with weeks

Could you help me creating a measure that calculates me the info of YoY based on weeks. I would need a new column in front of final revenue that indicates the revenue of past fiscal year  on the same quarter and week

Capture.PNG

12 REPLIES 12
Ashish_Mathur
Super User III
Super User III

Hi,

 

We must have a Calendar Table running from the first date appearing in the Date column of your Table to the last date in that column.  In the Calendar Table, there should also be a Week column.  Can you do that?  Also, what is your financial year (from which month to which month)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, Our Fiscal Calendar starts 1st November and ends 31 of October. Also our weeks are Sunday from Saturday and i need to split them in weeks 

Hi,

 

In the Calendar Table, can you create a weeek number column?  I will need that.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

This is the new file with date table"M_Dates" and with "Week"Column. Thanks for your help

 

LINK

Hi,

 

My formula in this file gives no result because the weeks/quarters and FY are not properly aligned between the M_dates table and the Date table.  Once you corect for that, my formula will work well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

What do you mean when you said they no are aligned. This calendar Date is a customized table with our weeks, months and FY

Hi,

 

By that i mean that the week number and/or FY and/or quarter appearing against a particular date in the Data Table is not the same as the one on the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Actually that is my problem because we have customized dates in our FY and that way is how they are set

Hi,

 

Actually we do not need to have FY, quarters and weeks at all in yout base data sheet.  These columns should only be in your Calendar Table.  The Actual date column in your base data sheet should bear a relationship to the Date column in the Calendar Table.  So it is important that the FY, quarters and weeks in the Calendar Table should be correct.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @espinale ,

 

Please try the measure below.

 

PY = 
CALCULATE (
    SUM ( 'table'[Revenue] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'table'[Year]
            = MAX ( 'table'[Year] ) - 1
            && 'table'[WeekNum] = MAX ( 'table'[WeekNum] )
            && 'table'[Qtr] = MAX ( 'table'[Qtr] )
    )
)

Here is the output.

 

output.PNG

More details, please refer to my test pbix which has been attached.

 

If you still need help, please share your data sample and your desired output so that we could help further on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is the Link  PBX

I tried that solution but It's not working. I can attach the PBX file but i dont see the option, if you teach me i can do it


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors