cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Compare data in current 4 week period VS same 4 week period last year

Hello, I am working with a data set that is reported every 4 weeks. Each 4 weeks I have converted into a period and 13 periods make up a year. Due to the calendar day differences the exact dates may not line up period 4 in 2016 was April 17th, and period 4 in 2017 was April 16th. I have been trying to use the formula for same period last year but not sure how to reference the time I need to reference from last year. 

 

The result I am looking for is a customer calculation to bring in to provide the logic of Brand A was 14 in period 4 2016 and 22 in preiod 4, 2017 = 8. I want to calculate the difference between 22-14=8.

 

same period last year= CALCULATE(SUM('Table'[Number]),DATEADD('Table'[Period],-1,YEAR))

 

GeographyBrandNumberYearQuarterMonthDayQuad Period in YearYear
USABrand A222017Qtr 2April1642017
USABrand B22017Qtr 2April1642017
USABrand C1312017Qtr 2April1642017
USABrand A142016Qtr 2April1742016
USABrand B42016Qtr 2April1742016
USABrand C1222016Qtr 2April1742016
12 REPLIES 12
Highlighted
Super User I
Super User I

Re: Compare data in current 4 week period VS same 4 week period last year

@jpt1228  have you got a separate date table have you created a field for  the customer periods ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: Compare data in current 4 week period VS same 4 week period last year

Hi @vanessafvg - I did not build a seperate date table, I added calculated columns in my data. Is there a difference? The Period in year 4 is the calculation for that 4 week time period.

Highlighted
Super User I
Super User I

Re: Compare data in current 4 week period VS same 4 week period last year

@jpt1228

 

having a separate date table makes a huge different, firstly from what i can see you have multiple rows per day, having a date table removes a multitude of confusing issues.

 

create one and map a relationship to your date

 

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Memorable Member
Memorable Member

Re: Compare data in current 4 week period VS same 4 week period last year

I understand your post - though I don't understand the data table you display.

 

But in essence; generically speaking - the data table we will assume has a transaction date field.  PBI can derive the year from that.  But the period is unique to you.  So you need to model your data table to add that period field via a calculated field/column - so that every record has its correct period value.

 

From there one can build a DAX statement - which depends on the actual fields involved.

www.CahabaData.com
Highlighted
Post Patron
Post Patron

Re: Compare data in current 4 week period VS same 4 week period last year

Ok, I think I am going in the right direction - I created a Date table using the range of dates in my fact table date column. Sample data below:

image.png

 

I want to pull in the value from last year for the same time, however the date is not exactly the same - January 24, 2016 and Janurary 22, 2017. They are the same 4 week time periods but in different years. Both of these dates would be considered period 1 in the year.

 

I created this formula to pull in last years value but I think I am having issues becasue the month date is not the same in both years

 

LY = CALCULATE(SUM('Smaller TDP Table'[TDP]), PREVIOUSYEAR('Smaller TDP Table'[Date]))

 

Thanks for your help!

 

Jon

 

 

 

Highlighted
Memorable Member
Memorable Member

Re: Compare data in current 4 week period VS same 4 week period last year

I will answer in this way: Prior Time Period comparison is an embedded feature/function within Power Bi / DAX - - based upon a recognized standard Date field.

 

The 'periods' as you describe - 4 week batches - is unique to your organization.  So you can't rely on the embedded features. 

 

It would be your task to correctly define period values for each date.  You can then compare.  But if the periods are not correctly equivalent between years - then it would also be your task to modify, such as having 2 columns with a 2016 period value and a 2017 period value. 

www.CahabaData.com
Highlighted
Post Patron
Post Patron

Re: Compare data in current 4 week period VS same 4 week period last year

Ok, so if I create a date table with a column for my custom 'Period' which I calculate for each year how do I create a calculated measure to give the result of 'period 2017' minus 'period 2016' to get the result? I am new to DAX so your help is greatly appreciated.

 

Thanks

Highlighted
Memorable Member
Memorable Member

Re: Compare data in current 4 week period VS same 4 week period last year

sorry to drive you crazy with a bunch of partial answers - but with code 'it depends'....

 

* are all the records in the same table, so that the comparison of the periods is within the same table - or - is the data reside in two different tables ( assume per year)

 

* is a period already aggregated so there is a single record with the sum for Period X - or - are there multiple records for period X that need to be summed first and then compared....

 

I know you posted some sample data in your original post - don't quite understand it's layout; generally 3-5 lines of the data table and an example of the desired result; will help the dax gurus give an example.....

www.CahabaData.com
Highlighted
Post Patron
Post Patron

Re: Compare data in current 4 week period VS same 4 week period last year

Hi @CahabaData Yes the data resides in the same table. The data is already aggregated into a date which is an aggregate of the previous 4 weeks of data. Here is how I pull the data. I am adding a column in the report that takes the date and finds the week in the year it is and then dividing that by 4 to convert the date to a period. The issue I am having in the date the data is aggregated for ends on different calendar dates from year to year.

 

 

Time DescriptionGeographyBrandTDP
Quad End 2017-Apr-16TOTAL USBrand A579
Quad End 2017-Mar-19TOTAL USBrand A565
Quad End 2017-Feb-19TOTAL USBrand A552
Quad End 2017-Jan-22TOTAL USBrand A518
Quad End 2016-Dec-25TOTAL USBrand A513
Quad End 2016-Nov-27TOTAL USBrand A516
Quad End 2016-Oct-30TOTAL USBrand A515
Quad End 2016-Oct-02TOTAL USBrand A515
Quad End 2016-Sep-04TOTAL USBrand A511
Quad End 2016-Aug-07TOTAL USBrand A507
Quad End 2016-Jul-10TOTAL USBrand A503
Quad End 2016-Jun-12TOTAL USBrand A503
Quad End 2016-May-15TOTAL USBrand A497
Quad End 2016-Apr-17TOTAL USBrand A463
Quad End 2016-Mar-20TOTAL USBrand A456
Quad End 2016-Feb-21TOTAL USBrand A436
Quad End 2016-Jan-24TOTAL USBrand A431

 

I am looking to calculate the TDP difference between Quad End 2017-Apr-16 and Quad End 2016-Apr 17. So I think I would filter the report on Quad End 2017-Apr-16 and see the current TDP and the change from the previous year for that same "Quad" time period.

 

Time DescriptionGeographyBrandTDP
Quad End 2017-Apr-16TOTAL USBrand A579
Quad End 2016-Apr-17TOTAL USBrand A463
   Total Change From Last Year-116

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors