Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need Some Help With Better Date Table Arrangements / structure

Hi there,

I have multiple sources of data that come into Power Bi around forecasting and such.  All the plans I bring in are all based on a week count, the Monday of each week.

I just can't get a clear hierarchy set up that would allow me to step up from the week by week view to a monthly view and then a yearly view which is ideal when we are looking at crew forecasts out to 2026.

I have a date table which just isn't right and not sure how to build up a correct date table that I can bind the 'date' value back to in order to step through the different periods I would like.

The one I have is based on a youtube video but either I am too old and dim to understand it properly and did something wrong (likely) or there was a step missing in the video.  I have seen various other options also and they sort of all do something different, is there a best practise that people abide by when it comes to date tables and setting up a date hierarchy.

 

Thank you

Need Help

2 Replies

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

    Anonymous , what solution do you want.

    Weeks are usually overlapping with month and year. so you can not drill to complete the week always,

     

    We have solution like month week , year week etc

     

    Start Year = STARTOFYEAR('Date'[Date],"3/31")
    WeekDay = WEEKDAY([Date],2) //monday
    Start of Week = [Date] -[WeekDay]+1 //monday
    FY Year = YEAR('Date'[Start Year]) // use end year
    FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1

     

     

    Start Month = STARTOMONTH('Date'[Date]) // or // eomonth([Date],-1)+0
    WeekDay = WEEKDAY([Date],2) //monday
    Start of Week = [Date] -[WeekDay]+1 //monday
    Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

     

    But based on exact need can share more

     

    .Week That Resets Yearly
    https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3-5-Power-BI/ba-p/1187502

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I don't quite understand what you mean, can you express your needs in the form of data? I also searched some introductions for you ,maybe you can refer to them and have a better understanding of date hierarchy .

    https://codingsight.com/create-and-use-custom-date-hierarchies-in-power-bi/

    https://spreadsheeto.com/power-bi-hierarchy/

     

    Best Regard

    Community Support Team _ Ailsa Tao

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