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

Comparing data by week and the same week number from a previous year.

I have a table in PBI with date annd have extracted the year and week number for each date into separate columns.

I can graph my data which counts all created records by week and I have a slicer that will allow me to select a year- all good. Columns are Created_date, Week No. and Year.

 

However I need to be able to plot the previous years data for the same weekly periods from the prior year on the same graph and this is where I am running into trouble. I am new to PBI and a little lost. I gather I need to create new measures?

 

 

so For example if I select 2018 for created records it shows me by week all 2018 records and also all 2017 records for the same week number.

 

Any help or guidance would be greatly appreciated.

9 REPLIES 9
DLimerick Frequent Visitor
Frequent Visitor

Getting totals for weeks last year.

HI All,

 

I am trying to graph created reports this year by week to created reports for the same weekly periods last yeat. My table has columns Created_Date, Week No., Year. I can see this years breakout but not sure how to get the totals for the same week numbers last year. I assume this is done through creating measures. I have a slicer that can break this out by year. If i choose 2018 i want o be able to see 2018 weekly numbersa and also the weekly totals for 2017.

 

I'm new to Power Bi and would greatly appreciate any help or guidance.

 

 

Community Support Team
Community Support Team

Re: Comparing data by week and the same week number from a previous year.

Hi @DLimerick

Measure here can get the same value for the same week last year

Measure = CALCULATE(MAX([Created_date]),SAMEPERIODLASTYEAR(Sheet1[Created_date]))

1.png

 

Best Regards

Maggie

DLimerick Frequent Visitor
Frequent Visitor

Re: Comparing data by week and the same week number from a previous year.

Thank you Maggie,

 

However what I am looking to do is to determine the week number of each date from the previous year. for example 7th Januuary 2017 might be week number 1 in 2017 and 7th January 2018 might be week number 2. I want to compare all dates in week one last year with all dates in week 1 this year. I hope that makes sense.

 

 

 

Community Support Team
Community Support Team

Re: Comparing data by week and the same week number from a previous year.

Hi @DLimerick

Yes, after nodifying this formula with the following one , it can make sense for your example.

Measure = CALCULATE(MAX([Week No.]),SAMEPERIODLASTYEAR(Sheet1[Created_date]))

1.png

 

Best Regards

Maggie

DLimerick Frequent Visitor
Frequent Visitor

Re: Comparing data by week and the same week number from a previous year.

Thank you Maggie,

 

I get  messgae - Calculation error in measure. a date column cntaining duplicate dates was specified in the call to function'SAMEPERIODLASTYEAR'. This is not supported.

 

I have a table already containing the records from last year and this year. I want it to identify these records so they can be plotted against this years records for the same week numbers.

Community Support Team
Community Support Team

Re: Comparing data by week and the same week number from a previous year.

Hi @DLimerick

"Calculation error in measure. a date column cntaining duplicate dates was specified in the call to function'SAMEPERIODLASTYEAR'. This is not supported."

The date column in your dataset contains duplicate values, for example, there are two rows of 2018/1/2, right?

 

Best Regards

Maggie



 

yacatac Frequent Visitor
Frequent Visitor

Re: Comparing data by week and the same week number from a previous year.

Hi,

 

I'd like to restart this thread as I have this exact issue. We would have multiple dates in the source data. However, our periods are 4/4/5 and would like to compare prior week, prior year week, and prior year YTD (by week).

 

Would this be possible?

 

TIA,

Stuart

Community Support Team
Community Support Team

Re: Comparing data by week and the same week number from a previous year.

Hi @yacatac

Please open another post so more people may see and more solutions will be provided.

 

Best Regards

Maggie

yacatac Frequent Visitor
Frequent Visitor

Re: Comparing data by week and the same week number from a previous year.

Will do, however the original post hadn't been solved, so I thought other people who have the same issue wouldn't want to trawl through numerous posts to get an answer.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 335 members 3,224 guests
Please welcome our newest community members: