Changing PeriodID to Fiscal periods

Reed9050

New Member
In Power Query I have a PeriodID column that starts at 1 and increases by 1 every 28 days, so each fiscal period has a unique number. I am trying to find a way to convert these numbers to periods, so it will repeat number 1 through 13 every year. Here is my current code:

Table.AddColumn(#"Added Custom7", "PeriodID", each if ([Index]>=2157 and [Index]<=2191) then 78 else Number.RoundDown(([Index]-1)/28)+1)
*P13 of 2017 has 5 weeks so I had to put the if statement in there.

Would it be easier to do this in power pivot? or is there a function that will allow me to "reset" count for each new fiscal year?
 

Reed9050

New Member
The calendar goes back to 12/2/2012, the start of the 2012 fiscal year. The next fiscal year starts after 13 28-day periods have elapsed. Except for 2017, where P13 was a 35-day period. I have a unique ID column for the Fiscal year and the period, if there is a formula that causes loops the values between 1 and 13 I can get periods from the PeriodID column, Thanks.
 

Some videos you may like

This Week's Hot Topics

Top