locked
Having trouble adding a Fiscal Year custom column and a Fiscal Week of Year custom column RRS feed

  • Question

  • The fiscal year runs from May through to April and I'm trying to add custom columns (using DAX) to represent this by year and by week (in PowerBI).

    Initially getting the Fiscal Year was no problem, but when I tried to break it down more I could not work out the Fiscal weeks (starting on the first the first Monday)

    Fiscal Year = CONCATENATE("FY ",IF('Calendar'[Month]>4,'Calendar'[Year],'Calendar'[Year]-1))

    I worked out how to do the week of the year (using ISO weeks) until I noticed that it is not in line with the dates being used by the accounting department (this completely confused me). Their weeks do start on Monday but they were not using the ISO week numbering. I also had to try and account for leap years which broke the formula! Can anyone please recommend a solution. I'mm struggling to find a solution online that works for my situation. 

    Here are the start date of a Fiscal Years.


    Start Date Days Weeks
    2014/04/28 364 52
    2015/04/27 364 52
    2016/04/25* 371 53
    2017/05/01 364 52
    2018/04/30 364 52
    2019/04/29 364 52
    2020/04/27 364 52
    2021/04/26* 364 52
    2022/04/25* 371 53
    2023/05/01 364 52
    2024/04/29 364 52
    2025/04/28 364 52
    2026/04/27 364 52
    2027/04/26* 371 53
    2028/05/01 364 52

    The dates marked with the * are the ones giving me trouble. Week 1 for each year should start on those dates.


    • Edited by Petri Blaauw Thursday, February 23, 2017 9:08 AM spelling
    Thursday, February 23, 2017 9:05 AM

Answers

  • Hi Petri,

    You'd better create a Calendar Table, create relationship between your Calendar Table and Fact table. First, get ISO weeks in Calendar Table, then create calculated column in fact table using the following formula to get weeks number.

    week=RELATED(Calendar[week-number])

    Please let me know if you have any question.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 24, 2017 1:59 AM

All replies

  • Hi Petri,

    You'd better create a Calendar Table, create relationship between your Calendar Table and Fact table. First, get ISO weeks in Calendar Table, then create calculated column in fact table using the following formula to get weeks number.

    week=RELATED(Calendar[week-number])

    Please let me know if you have any question.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 24, 2017 1:59 AM
  • Yes, I have a date dimension (Calendar) that contains calculated columns for the ISO Year and week. 

    The current solution I'm using is as follows:

    Using a file they provided me, containing their Fiscal dates I generated a Fiscal(date) table in Excel for a 40-year date range. Imported that into my cube and set it not to update on refresh. Then merged the Fiscal dimension into my Date dimension. So from a user point-of-view, they only see the 1 date dimension.

    This does work and does not require too much maintenance, but I would like to know of a more automated way to achieve the same results.


    Tuesday, March 7, 2017 2:32 PM
  • Hi Petri,

    Based on my understanding, you create a calculated column in your resource table at one time. After that you select your expected result.

    >>So from a user point-of-view, they only see the 1 date dimension

    Your users are only see part of date, right? You add a filter and want it run automatically? Your resource have large volumes, so you want to a high efficient way?

    Best Regards,
    Angeliea


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 10, 2017 3:18 AM
  • Not sure if I understand your question correctly.

    My date dimension contains data from 2014 to current (DateTime.LocalNow())) and the fiscal dimension contains data from 2014 to eg. 2040.

    When merging fiscal to date it only shows fiscal dates up to current. But when it refreshed the next day it will include the fiscal details for that day as well. So the users only see the date for which there is data available in my fact tables.

    Friday, March 10, 2017 1:10 PM
  • Hi Petri,

    pls check if this helps: https://community.powerbi.com/t5/Desktop/Custom-Fiscal-Year-Calendar/td-p/85228


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    • Proposed as answer by Michael Amadi Wednesday, August 9, 2017 6:42 AM
    Saturday, April 22, 2017 6:04 AM
    Answerer