Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 in custom terms and weeks etc.
In my normal date table, week numbers etc are no problem but I'm having an issue making them reoccur by term.
I've watched/read heaps of info on this but I can't seem to apply it to my model.
Anyone's help would be much appreciated.
Thank you
@Anonymous what is the logic to create these column? Can you provide some details?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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/2019 | Term 1 | Week 1 |
30/01/2019 | Term 1 | Week 1 |
31/01/2019 | Term 1 | Week 1 |
1/02/2019 | Term 1 | Week 1 |
2/02/2019 | Term 1 | Week 1 |
3/02/2019 | Term 1 | Week 1 |
4/02/2019 | Term 1 | Week 2 |
5/02/2019 | Term 1 | Week 2 |
6/02/2019 | Term 1 | Week 2 |
7/02/2019 | Term 1 | Week 2 |
8/02/2019 | Term 1 | Week 2 |
9/02/2019 | Term 1 | Week 2 |
10/02/2019 | Term 1 | Week 2 |
11/02/2019 | Term 1 | Week 3 |
12/02/2019 | Term 1 | Week 3 |
13/02/2019 | Term 1 | Week 3 |
14/02/2019 | Term 1 | Week 3 |
15/02/2019 | Term 1 | Week 3 |
16/02/2019 | Term 1 | Week 3 |
17/02/2019 | Term 1 | Week 3 |
18/02/2019 | Term 1 | Week 4 |
19/02/2019 | Term 1 | Week 4 |
20/02/2019 | Term 1 | Week 4 |
21/02/2019 | Term 1 | Week 4 |
22/02/2019 | Term 1 | Week 4 |
23/02/2019 | Term 1 | Week 4 |
24/02/2019 | Term 1 | Week 4 |
25/02/2019 | Term 1 | Week 5 |
26/02/2019 | Term 1 | Week 5 |
27/02/2019 | Term 1 | Week 5 |
28/02/2019 | Term 1 | Week 5 |
1/03/2019 | Term 1 | Week 5 |
2/03/2019 | Term 1 | Week 5 |
3/03/2019 | Term 1 | Week 5 |
4/03/2019 | Term 1 | Week 6 |
5/03/2019 | Term 1 | Week 6 |
6/03/2019 | Term 1 | Week 6 |
7/03/2019 | Term 1 | Week 6 |
8/03/2019 | Term 1 | Week 6 |
9/03/2019 | Term 1 | Week 6 |
10/03/2019 | Term 1 | Week 6 |
11/03/2019 | Term 1 | Week 7 |
12/03/2019 | Term 1 | Week 7 |
13/03/2019 | Term 1 | Week 7 |
14/03/2019 | Term 1 | Week 7 |
15/03/2019 | Term 1 | Week 7 |
16/03/2019 | Term 1 | Week 7 |
17/03/2019 | Term 1 | Week 7 |
18/03/2019 | Term 1 | Week 8 |
19/03/2019 | Term 1 | Week 8 |
20/03/2019 | Term 1 | Week 8 |
21/03/2019 | Term 1 | Week 8 |
22/03/2019 | Term 1 | Week 8 |
23/03/2019 | Term 1 | Week 8 |
24/03/2019 | Term 1 | Week 8 |
25/03/2019 | Term 1 | Week 9 |
26/03/2019 | Term 1 | Week 9 |
27/03/2019 | Term 1 | Week 9 |
28/03/2019 | Term 1 | Week 9 |
29/03/2019 | Term 1 | Week 9 |
30/03/2019 | Term 1 | Week 9 |
31/03/2019 | Term 1 | Week 9 |
1/04/2019 | Term 1 | Week 10 |
2/04/2019 | Term 1 | Week 10 |
3/04/2019 | Term 1 | Week 10 |
4/04/2019 | Term 1 | Week 10 |
5/04/2019 | Term 1 | Week 10 |
23/04/2019 | Term 2 | Week 1 |
24/04/2019 | Term 2 | Week 1 |
25/04/2019 | Term 2 | Week 1 |
26/04/2019 | Term 2 | Week 1 |
27/04/2019 | Term 2 | Week 1 |
28/04/2019 | Term 2 | Week 1 |
29/04/2019 | Term 2 | Week 2 |
30/04/2019 | Term 2 | Week 2 |
1/05/2019 | Term 2 | Week 2 |
2/05/2019 | Term 2 | Week 2 |
3/05/2019 | Term 2 | Week 2 |
4/05/2019 | Term 2 | Week 2 |
5/05/2019 | Term 2 | Week 2 |
6/05/2019 | Term 2 | Week 3 |
7/05/2019 | Term 2 | Week 3 |
8/05/2019 | Term 2 | Week 3 |
9/05/2019 | Term 2 | Week 3 |
10/05/2019 | Term 2 | Week 3 |
11/05/2019 | Term 2 | Week 3 |
12/05/2019 | Term 2 | Week 3 |
13/05/2019 | Term 2 | Week 4 |
14/05/2019 | Term 2 | Week 4 |
15/05/2019 | Term 2 | Week 4 |
16/05/2019 | Term 2 | Week 4 |
17/05/2019 | Term 2 | Week 4 |
18/05/2019 | Term 2 | Week 4 |
19/05/2019 | Term 2 | Week 4 |
20/05/2019 | Term 2 | Week 5 |
21/05/2019 | Term 2 | Week 5 |
22/05/2019 | Term 2 | Week 5 |
23/05/2019 | Term 2 | Week 5 |
24/05/2019 | Term 2 | Week 5 |
25/05/2019 | Term 2 | Week 5 |
26/05/2019 | Term 2 | Week 5 |
27/05/2019 | Term 2 | Week 6 |
28/05/2019 | Term 2 | Week 6 |
29/05/2019 | Term 2 | Week 6 |
30/05/2019 | Term 2 | Week 6 |
31/05/2019 | Term 2 | Week 6 |
1/06/2019 | Term 2 | Week 6 |
2/06/2019 | Term 2 | Week 6 |
3/06/2019 | Term 2 | Week 7 |
4/06/2019 | Term 2 | Week 7 |
5/06/2019 | Term 2 | Week 7 |
6/06/2019 | Term 2 | Week 7 |
7/06/2019 | Term 2 | Week 7 |
8/06/2019 | Term 2 | Week 7 |
9/06/2019 | Term 2 | Week 7 |
10/06/2019 | Term 2 | Week 8 |
11/06/2019 | Term 2 | Week 8 |
12/06/2019 | Term 2 | Week 8 |
13/06/2019 | Term 2 | Week 8 |
14/06/2019 | Term 2 | Week 8 |
15/06/2019 | Term 2 | Week 8 |
16/06/2019 | Term 2 | Week 8 |
17/06/2019 | Term 2 | Week 9 |
18/06/2019 | Term 2 | Week 9 |
19/06/2019 | Term 2 | Week 9 |
20/06/2019 | Term 2 | Week 9 |
21/06/2019 | Term 2 | Week 9 |
And would continue on for the whole year.
Thank you!
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
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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |