cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Memorable Member
Memorable Member

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

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
Highlighted
Post Patron
Post Patron

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

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

Highlighted
Post Patron
Post Patron

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

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

 

 

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
Top Kudoed Authors