Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpt1228
Responsive Resident
Responsive Resident

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
CahabaData
Memorable Member
Memorable Member

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

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

 

 

 

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

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

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

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

boy if only you could do month  rather than quads.  so we are kind of going full circle here.  No way for code to know how to match those 2 time periods; you need a year column and a period column

 

an early reply suggested this in a separate table - which works - or you could add to this table.

 

that time measure field is surely a string - - you have to parse that apart to derive the year - that's just a little work in the query editor....

 

the problem is the period; I see no programmatic way to know that those 2 in your example are the same quad/period value...one can't +1 in every case.... it would almost seems like it is going to have be manually built.

 

this is the first step for sure......

 

www.CahabaData.com

Yes, month would be a piece of cake. I have been working on figuring out how to calculate these but beginning to think I should just use the interface that I pull the data from.

 

I took the raw data table as I just posted and added a column for Year, Week in Year, and Week in year/4 to get the period (I omitted that in the example when you wanted to see the raw data). That works to convert the Quad(date) into a period. Is there a way to basically do this: Period 4 2017 - Period 4 2016 = -116?

 

image.png

jpt1228
Responsive Resident
Responsive Resident

I have received a suggestion to calculate the ISO 8601 week number in the year and then reference the sale ISO week num from last year. How do I tell DAX to take the current year week in year from the date table and return the value in the fact table from the same week one year ago?

 

ISO Week Num in Year = weeknum('Date DIM'[Date],21)

 

TDP LY = CALCULATE(SUM(TDP[TDP]), PARALLELPERIOD('Date DIM'[ISO Week Num in Year], -1, YEAR))

 

Thanks

 

Jon

 

 

vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

@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/

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.