Forum Discussion

jpt1228's avatar
jpt1228
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

Custom Fiscal Weeks Add to DImDate Table

Hello - I have been working to create a custom DimDate table based on custom fiscal periods that are different than calendar. I have 13 Fiscal Periods consisting of 28 days per period. I want to add Week in Fiscal Period, and Days Elapsed in Fiscal Period. Both of these columns would reset at the beginning of each Fiscal Period.

 

Thanks

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi jpt1228 ,

    You can create the following calculated columns to solve your problem:

     

    Days Elapsed in Fiscal Period = DATEDIFF(DimDate[FiscPeriodStart],DimDate[Date],DAY) +1

     

    Week in Fiscal Period = CEILING(DIVIDE(DimDate[Days Elapsed in Fiscal Period],7),1)

     

    You will get the following result:

     

    Here is the demo, please try it:

    PBIX 

     

    Best Regards,

    Yingjie Li

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Created three columns like this. Hope these can help

     

     

    Fiscal Year Start = minx(FILTER(DimDate,earlier(DimDate[Year])=DimDate[Year]),DimDate[FiscPeriodStart])
Days elapsed in Fiscal year = DATEDIFF(DimDate[Fiscal Year Start],DimDate[Date],DAY) +1
Week Fiscal Year = QUOTIENT(DimDate[Days elapsed in Fiscal year],7)+1

     

    refer : https://www.dropbox.com/s/9tzsrvok5l62es5/DimDate%20Custom%20Calendar.pbix?dl=0

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • jpt1228's avatar
      jpt1228
      Icon for Responsive Resident rankResponsive Resident
      6 years ago

      Hello amitchandak  thanks for your reply. I fixed one small part of the formula - The original formula was looking at the calendar year. It was off by 2-3 days depending on the year. Changed to FiscalYear and works beautifully.

       

       

      Fiscal Year Start = minx(FILTER(DimDate,earlier(DimDate[Year])=DimDate[Year]),DimDate[FiscPeriodStart])

Changed Year to FiscalYear

Fiscal Year Start = minx(FILTER(DimDate,earlier(DimDate[FiscalYear])=DimDate[FiscalYear]),DimDate[FiscPeriodStart])

       

       

       

      I think with these new columns added this Power BI custom fiscal calendar for non-standard dates is the most complete I have found on the Internet anywhere. thanks to amitchandak for the starting point and a few additional columns and v-yingjl  for the remaining calculations. what could make it even better is to add the columns after the M-Code into the M-Code, but I am not good enough with M to do that.

       

      PBI Calendar for Non-Standard Fiscal Dates and Periods 

       

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi jpt1228 ,

    You can create the following calculated columns to solve your problem:

     

    Days Elapsed in Fiscal Period = DATEDIFF(DimDate[FiscPeriodStart],DimDate[Date],DAY) +1

     

    Week in Fiscal Period = CEILING(DIVIDE(DimDate[Days Elapsed in Fiscal Period],7),1)

     

    You will get the following result:

     

    Here is the demo, please try it:

    PBIX 

     

    Best Regards,

    Yingjie Li

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.