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
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous what is the logic to create these column? Can you provide some details?

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