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
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)?
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
In the Calendar Table, can you create a weeek number column? I will need that.
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.
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.
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.
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.
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.
Click here to learn more about the September 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!