Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Custom date table set via school semesters/terms/weeks

Hi all   Does anyone know how to add custom semester numbers/term numbers and week numbers to a date table?   I have no problem creating comprehensive date tables but I'm really struggling adding...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi parry2k

 

This particular example is to produce a transport related report.

 

I need to track passenger numbers, arrival/departure times, trip durations etc.

 

The only issue is schools tend to run via weeks in a term e.g. Term 2 - Week 3/Term 4 - Week 8 etc

 

So I need to be able to track the data across weeks in a term, Terms in a year and Semesters per year.

 

Example:

 

Term 1 = 29th Jan 2019 to 5th April 2019 (10 weeks)

Term 2 = 23rd April to 21st June 2019 (9 weeks) and so on and so forth

 

I could create a slicer to replicate the groupings using days/months but it's not polished enough for what I'm trying to achieve.

 

I have managed to create what I need for a single term using WEEKNUM +/- but trying to get that to repeat including terms which make the weeks start again from zero has me stumped.

 

Appreciate your help!

parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous can you put example in excel sheet and share it? You can share it via one drive/google drive or by email which is in my signature. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi parry2k 

     

    The table needs to look exactly like the below table (I can extract any other info that I need). I'd appreciate your help to be able to build this within PowerBI!

     

    29/01/2019Term 1Week 1
    30/01/2019Term 1Week 1
    31/01/2019Term 1Week 1
    1/02/2019Term 1Week 1
    2/02/2019Term 1Week 1
    3/02/2019Term 1Week 1
    4/02/2019Term 1Week 2
    5/02/2019Term 1Week 2
    6/02/2019Term 1Week 2
    7/02/2019Term 1Week 2
    8/02/2019Term 1Week 2
    9/02/2019Term 1Week 2
    10/02/2019Term 1Week 2
    11/02/2019Term 1Week 3
    12/02/2019Term 1Week 3
    13/02/2019Term 1Week 3
    14/02/2019Term 1Week 3
    15/02/2019Term 1Week 3
    16/02/2019Term 1Week 3
    17/02/2019Term 1Week 3
    18/02/2019Term 1Week 4
    19/02/2019Term 1Week 4
    20/02/2019Term 1Week 4
    21/02/2019Term 1Week 4
    22/02/2019Term 1Week 4
    23/02/2019Term 1Week 4
    24/02/2019Term 1Week 4
    25/02/2019Term 1Week 5
    26/02/2019Term 1Week 5
    27/02/2019Term 1Week 5
    28/02/2019Term 1Week 5
    1/03/2019Term 1Week 5
    2/03/2019Term 1Week 5
    3/03/2019Term 1Week 5
    4/03/2019Term 1Week 6
    5/03/2019Term 1Week 6
    6/03/2019Term 1Week 6
    7/03/2019Term 1Week 6
    8/03/2019Term 1Week 6
    9/03/2019Term 1Week 6
    10/03/2019Term 1Week 6
    11/03/2019Term 1Week 7
    12/03/2019Term 1Week 7
    13/03/2019Term 1Week 7
    14/03/2019Term 1Week 7
    15/03/2019Term 1Week 7
    16/03/2019Term 1Week 7
    17/03/2019Term 1Week 7
    18/03/2019Term 1Week 8
    19/03/2019Term 1Week 8
    20/03/2019Term 1Week 8
    21/03/2019Term 1Week 8
    22/03/2019Term 1Week 8
    23/03/2019Term 1Week 8
    24/03/2019Term 1Week 8
    25/03/2019Term 1Week 9
    26/03/2019Term 1Week 9
    27/03/2019Term 1Week 9
    28/03/2019Term 1Week 9
    29/03/2019Term 1Week 9
    30/03/2019Term 1Week 9
    31/03/2019Term 1Week 9
    1/04/2019Term 1Week 10
    2/04/2019Term 1Week 10
    3/04/2019Term 1Week 10
    4/04/2019Term 1Week 10
    5/04/2019Term 1Week 10
    23/04/2019Term 2Week 1
    24/04/2019Term 2Week 1
    25/04/2019Term 2Week 1
    26/04/2019Term 2Week 1
    27/04/2019Term 2Week 1
    28/04/2019Term 2Week 1
    29/04/2019Term 2Week 2
    30/04/2019Term 2Week 2
    1/05/2019Term 2Week 2
    2/05/2019Term 2Week 2
    3/05/2019Term 2Week 2
    4/05/2019Term 2Week 2
    5/05/2019Term 2Week 2
    6/05/2019Term 2Week 3
    7/05/2019Term 2Week 3
    8/05/2019Term 2Week 3
    9/05/2019Term 2Week 3
    10/05/2019Term 2Week 3
    11/05/2019Term 2Week 3
    12/05/2019Term 2Week 3
    13/05/2019Term 2Week 4
    14/05/2019Term 2Week 4
    15/05/2019Term 2Week 4
    16/05/2019Term 2Week 4
    17/05/2019Term 2Week 4
    18/05/2019Term 2Week 4
    19/05/2019Term 2Week 4
    20/05/2019Term 2Week 5
    21/05/2019Term 2Week 5
    22/05/2019Term 2Week 5
    23/05/2019Term 2Week 5
    24/05/2019Term 2Week 5
    25/05/2019Term 2Week 5
    26/05/2019Term 2Week 5
    27/05/2019Term 2Week 6
    28/05/2019Term 2Week 6
    29/05/2019Term 2Week 6
    30/05/2019Term 2Week 6
    31/05/2019Term 2Week 6
    1/06/2019Term 2Week 6
    2/06/2019Term 2Week 6
    3/06/2019Term 2Week 7
    4/06/2019Term 2Week 7
    5/06/2019Term 2Week 7
    6/06/2019Term 2Week 7
    7/06/2019Term 2Week 7
    8/06/2019Term 2Week 7
    9/06/2019Term 2Week 7
    10/06/2019Term 2Week 8
    11/06/2019Term 2Week 8
    12/06/2019Term 2Week 8
    13/06/2019Term 2Week 8
    14/06/2019Term 2Week 8
    15/06/2019Term 2Week 8
    16/06/2019Term 2Week 8
    17/06/2019Term 2Week 9
    18/06/2019Term 2Week 9
    19/06/2019Term 2Week 9
    20/06/2019Term 2Week 9
    21/06/2019Term 2Week 9

     

    And would continue on for the whole year.

     

    Thank you!

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous 

      Would you like to create a fixed school semesters/terms/weeks or dynamic ones?

      dynamic ones mean:

      select any date from a slicer, then look into serveral weeks before the spefic date?

       

      Best Regards
      Maggie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-juanli-msft 

        I was hoping for fixed if possible?

        Thank you!