Results 1 to 3 of 3

Thread: Changing PeriodID to Fiscal periods
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Changing PeriodID to Fiscal periods

    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?

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing PeriodID to Fiscal periods

    What day does your Fiscal Year start?

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing PeriodID to Fiscal periods

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •