Forum Discussion
Trading Periods Table showing Period and week
Hi Everyone
I am fairly new to Dax and PowerBi and need some help creating a data table that will show me periods throughout the year and weeks.
Basically our Fiscal year always starts on the 4th Sunday of January - know as P1 (Period 1). Therefore this year our P1 started on the 24th January.
We hvae 12 periods throughout the year - and each period has either 5 or 4 weeks.
P1=5
P2=4
P3=4
P4=5
P5=4.....4.....5.....4.....4......5
until we get to P11=4 and P12 = 5.
I need to create a table going back to 2012 that will work all of these dates, periods and weeks out for me. Also ideally it would need to show the year, period and week from the pervious year
Column Headers would be
Date, Day,DayName, Month, MonthName, Year, WeekNo, PeriodNo, PriorYear, PriorYPeriod, PriorYWeek
Is there any formula that would be able to work this out for me and create the table automatically?
Any help would be great
Thanks
LizardBoi , Once you are able to create week and period , you can create rank on those like
new columns in calendar table
year period = [year]*100 +[period]
year week= [year]*100 +[week]
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Then you can use measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))
3 Replies
- amitchandak
Super User
LizardBoi , Once you are able to create week and period , you can create rank on those like
new columns in calendar table
year period = [year]*100 +[period]
year week= [year]*100 +[week]
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Then you can use measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))Thanks - but how do I create the table for Year and Period?
HiLizardBoi,
You can create a new table like :
Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
or
Calendar = CALENDARAUTO()
For more information,you can refer to this :Creating-calendar-table-in-power-bi-using-dax-functions
Best regards,
Caitlyn Yan
