Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
Geography | Brand | Number | Year | Quarter | Month | Day | Quad Period in Year | Year |
USA | Brand A | 22 | 2017 | Qtr 2 | April | 16 | 4 | 2017 |
USA | Brand B | 2 | 2017 | Qtr 2 | April | 16 | 4 | 2017 |
USA | Brand C | 131 | 2017 | Qtr 2 | April | 16 | 4 | 2017 |
USA | Brand A | 14 | 2016 | Qtr 2 | April | 17 | 4 | 2016 |
USA | Brand B | 4 | 2016 | Qtr 2 | April | 17 | 4 | 2016 |
USA | Brand C | 122 | 2016 | Qtr 2 | April | 17 | 4 | 2016 |
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.
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:
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.
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.....
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 Description | Geography | Brand | TDP |
Quad End 2017-Apr-16 | TOTAL US | Brand A | 579 |
Quad End 2017-Mar-19 | TOTAL US | Brand A | 565 |
Quad End 2017-Feb-19 | TOTAL US | Brand A | 552 |
Quad End 2017-Jan-22 | TOTAL US | Brand A | 518 |
Quad End 2016-Dec-25 | TOTAL US | Brand A | 513 |
Quad End 2016-Nov-27 | TOTAL US | Brand A | 516 |
Quad End 2016-Oct-30 | TOTAL US | Brand A | 515 |
Quad End 2016-Oct-02 | TOTAL US | Brand A | 515 |
Quad End 2016-Sep-04 | TOTAL US | Brand A | 511 |
Quad End 2016-Aug-07 | TOTAL US | Brand A | 507 |
Quad End 2016-Jul-10 | TOTAL US | Brand A | 503 |
Quad End 2016-Jun-12 | TOTAL US | Brand A | 503 |
Quad End 2016-May-15 | TOTAL US | Brand A | 497 |
Quad End 2016-Apr-17 | TOTAL US | Brand A | 463 |
Quad End 2016-Mar-20 | TOTAL US | Brand A | 456 |
Quad End 2016-Feb-21 | TOTAL US | Brand A | 436 |
Quad End 2016-Jan-24 | TOTAL US | Brand A | 431 |
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 Description | Geography | Brand | TDP |
Quad End 2017-Apr-16 | TOTAL US | Brand A | 579 |
Quad End 2016-Apr-17 | TOTAL US | Brand A | 463 |
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......
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?
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
@jpt1228 have you got a separate date table have you created a field for the customer periods ?
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.
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/
Proud to be a Super User!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |