Measure of YoY with weeks

[Anonymous]

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

[Ashish_Mathur]

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)?

[Anonymous]

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 

[Ashish_Mathur]

Hi,

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

[Anonymous]

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

LINK

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

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.

[v-piga-msft]

Hi @Anonymous ,

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.

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

[Anonymous]

This is the Link  PBX

[Anonymous]

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

---