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 

 

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Maggie v-juanli-msft , were you able to have a think about this at all?

     

    I'd love to be able to solve it.

     

    parry2k , did you have any idea?

     

    Thanks guys

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

      Hi Anonymous 

      If i understand the rule i may be able to slove it.

      i don't understand,

      on my side, the first week of the year is 2019/1/1/~2019/1/6,

      but in your example, it is 2019/1/29.

      why term 1 has 10 weeks, term 2 has 9 weeks, how many weeks are there in term3?

      the start of term2 isn't the end of term1, there are some dates excluded in both terms.

       

      Best Regards
      Maggie

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Maggie v-juanli-msft , thanks for getting back to me!

         

        Term 1 starts on the 29th Jan because that's the start of the school year.

         

        The break in between Term 1 and Term 2 is a school holiday. The same will occur between every term.

         

        Schools tend to run their business based on a particular week in a term rather than a week in a year.

         

        Term 1 - Tuesday 29 January to Friday 5 April 2019 - 10 weeks

        Term 2 - Tuesday 23 April to Friday 21 June 2019 - 9 weeks

        Term 3 - Monday 15 July to Friday 20 September 2019 - 10 weeks

        Term 4 - Tuesday 8 October to Friday  29 November 2019 - 8 weeks

         

        If I extract the Week No. using WEEKNUM it shows the correct information because school still runs from Mon to Fri but the week numbering is incorrect.

         

        Does any of that make sense?