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.
I'm trying to create a custom calendar for wokr, i have seen there is plenty of advide on here, but none seem to cover my problem.
My employer breaks the financial year into 13 periods and those 13 periods are made up of 4 weeks so today for instance we are in period 11 week 42.
is there a way to translate this into power bi?
I have added a screen shot of qhat I'm currently using in excel.
Can I add my knowlegde of DAX is limited so be gentle 😁
Solved! Go to Solution.
@billyrich83 Well, you could just use an Enter data query and copy and paste from Excel instead of the fancy query transformation
I would add a column for Period in the Calendar table
I have a complete calendar table with 1 row per day.
The code looks like this:
Period =
ROUNDUP(
DIVIDE(
'Calendar'[Week No],
4),
0
I used my calendar template here for the Calendar table:
https://www.villezekeviking.com/dax-tables-calendar-and-time/
Please see if this article is helpful.
445 Calendar with 53-Week Years – Hoosier BI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@billyrich83 Well, I assume you have a date table already. If not, then you could create one using the CALENDAR function. Let's call this table "Dates". Then I would import your table from Excel, let's call this table "Fiscal". Now, given your table layout, you might have to get a little fancy on the import like unpivoting your last six columns or importing your first four columns in a query. Then another query where you grab columns 1,2,5,6 and a third for 1,2,7,8. You would then append all of those together and disable load of the 3 intermediate queries. If you post your fiscal data as text I could create the sample query/transformations for you.
You could then create calculated columns in your Dates table like this:
Week No =
VAR __CurrentRowDate = [Date]
VAR __PeriodLookup = MAXX(FILTER('Fiscal',[Period Start Date] <= [Date]),[Period Start Date])
VAR __WeekNo = MAXX(FILTER('Fiscal',[Period Start Date] = __PeriodLookup),[WeekNo])
RETURN
__WeekNo
Period =
VAR __CurrentRowDate = [Date]
VAR __PeriodLookup = MAXX(FILTER('Fiscal',[Period Start Date] <= [Date]),[Period Start Date])
VAR __Period = MAXX(FILTER('Fiscal',[Period Start Date] = __PeriodLookup),[Period])
RETURN
__Period
Hi Greg,
Thanks for your response whilst I was waiting I was having a play about and have got this far in PowerBi.
Is there a way to get this to work for me it may be easier than importing my excel data?
@billyrich83 Well, you could just use an Enter data query and copy and paste from Excel instead of the fancy query transformation
Hi Greg,
Thanks again, I'm just making my life awkward. fiscal data below upto end of march this year. it doens't pick up when the year as a 53rd week
Week No | Period | Period Start Date | Year 19/20 | Period Start Date | Year 20/21 | Period Start Date | Year 21/22 |
1 | 1 | 01/04/2019 | 2019/20 | 06/04/2020 | 2020/21 | 05/04/2021 | 2021/22 |
2 | 1 | 08/04/2019 | 2019/20 | 13/04/2020 | 2020/21 | 12/04/2021 | 2021/22 |
3 | 1 | 15/04/2019 | 2019/20 | 20/04/2020 | 2020/21 | 19/04/2021 | 2021/22 |
4 | 1 | 22/04/2019 | 2019/20 | 27/04/2020 | 2020/21 | 26/04/2021 | 2021/22 |
5 | 2 | 29/04/2019 | 2019/20 | 04/05/2020 | 2020/21 | 03/05/2021 | 2021/22 |
6 | 2 | 06/05/2019 | 2019/20 | 11/05/2020 | 2020/21 | 10/05/2021 | 2021/22 |
7 | 2 | 13/05/2019 | 2019/20 | 18/05/2020 | 2020/21 | 17/05/2021 | 2021/22 |
8 | 2 | 20/05/2019 | 2019/20 | 25/05/2020 | 2020/21 | 24/05/2021 | 2021/22 |
9 | 3 | 27/05/2019 | 2019/20 | 01/06/2020 | 2020/21 | 31/05/2021 | 2021/22 |
10 | 3 | 03/06/2019 | 2019/20 | 08/06/2020 | 2020/21 | 07/06/2021 | 2021/22 |
11 | 3 | 10/06/2019 | 2019/20 | 15/06/2020 | 2020/21 | 14/06/2021 | 2021/22 |
12 | 3 | 17/06/2019 | 2019/20 | 22/06/2020 | 2020/21 | 21/06/2021 | 2021/22 |
13 | 4 | 24/06/2019 | 2019/20 | 29/06/2020 | 2020/21 | 28/06/2021 | 2021/22 |
14 | 4 | 01/07/2019 | 2019/20 | 06/07/2020 | 2020/21 | 05/07/2021 | 2021/22 |
15 | 4 | 08/07/2019 | 2019/20 | 13/07/2020 | 2020/21 | 12/07/2021 | 2021/22 |
16 | 4 | 15/07/2019 | 2019/20 | 20/07/2020 | 2020/21 | 19/07/2021 | 2021/22 |
17 | 5 | 22/07/2019 | 2019/20 | 27/07/2020 | 2020/21 | 26/07/2021 | 2021/22 |
18 | 5 | 29/07/2019 | 2019/20 | 03/08/2020 | 2020/21 | 02/08/2021 | 2021/22 |
19 | 5 | 05/08/2019 | 2019/20 | 10/08/2020 | 2020/21 | 09/08/2021 | 2021/22 |
20 | 5 | 12/08/2019 | 2019/20 | 17/08/2020 | 2020/21 | 16/08/2021 | 2021/22 |
21 | 6 | 19/08/2019 | 2019/20 | 24/08/2020 | 2020/21 | 23/08/2021 | 2021/22 |
22 | 6 | 26/08/2019 | 2019/20 | 31/08/2020 | 2020/21 | 30/08/2021 | 2021/22 |
23 | 6 | 02/09/2019 | 2019/20 | 07/09/2020 | 2020/21 | 06/09/2021 | 2021/22 |
24 | 6 | 09/09/2019 | 2019/20 | 14/09/2020 | 2020/21 | 13/09/2021 | 2021/22 |
25 | 7 | 16/09/2019 | 2019/20 | 21/09/2020 | 2020/21 | 20/09/2021 | 2021/22 |
26 | 7 | 23/09/2019 | 2019/20 | 28/09/2020 | 2020/21 | 27/09/2021 | 2021/22 |
27 | 7 | 30/09/2019 | 2019/20 | 05/10/2020 | 2020/21 | 04/10/2021 | 2021/22 |
28 | 7 | 07/10/2019 | 2019/20 | 12/10/2020 | 2020/21 | 11/10/2021 | 2021/22 |
29 | 8 | 14/10/2019 | 2019/20 | 19/10/2020 | 2020/21 | 18/10/2021 | 2021/22 |
30 | 8 | 21/10/2019 | 2019/20 | 26/10/2020 | 2020/21 | 25/10/2021 | 2021/22 |
31 | 8 | 28/10/2019 | 2019/20 | 02/11/2020 | 2020/21 | 01/11/2021 | 2021/22 |
32 | 8 | 04/11/2019 | 2019/20 | 09/11/2020 | 2020/21 | 08/11/2021 | 2021/22 |
33 | 9 | 11/11/2019 | 2019/20 | 16/11/2020 | 2020/21 | 15/11/2021 | 2021/22 |
34 | 9 | 18/11/2019 | 2019/20 | 23/11/2020 | 2020/21 | 22/11/2021 | 2021/22 |
35 | 9 | 25/11/2019 | 2019/20 | 30/11/2020 | 2020/21 | 29/11/2021 | 2021/22 |
36 | 9 | 02/12/2019 | 2019/20 | 07/12/2020 | 2020/21 | 06/12/2021 | 2021/22 |
37 | 10 | 09/12/2019 | 2019/20 | 14/12/2020 | 2020/21 | 13/12/2021 | 2021/22 |
38 | 10 | 16/12/2019 | 2019/20 | 21/12/2020 | 2020/21 | 20/12/2021 | 2021/22 |
39 | 10 | 23/12/2019 | 2019/20 | 28/12/2020 | 2020/21 | 27/12/2021 | 2021/22 |
40 | 10 | 30/12/2019 | 2019/20 | 04/01/2021 | 2020/21 | 03/01/2022 | 2021/22 |
41 | 11 | 06/01/2020 | 2019/20 | 11/01/2021 | 2020/21 | 10/01/2022 | 2021/22 |
42 | 11 | 13/01/2020 | 2019/20 | 18/01/2021 | 2020/21 | 17/01/2022 | 2021/22 |
43 | 11 | 20/01/2020 | 2019/20 | 25/01/2021 | 2020/21 | 24/01/2022 | 2021/22 |
44 | 11 | 27/01/2020 | 2019/20 | 01/02/2021 | 2020/21 | 31/01/2022 | 2021/22 |
45 | 12 | 03/02/2020 | 2019/20 | 08/02/2021 | 2020/21 | 07/02/2022 | 2021/22 |
46 | 12 | 10/02/2020 | 2019/20 | 15/02/2021 | 2020/21 | 14/02/2022 | 2021/22 |
47 | 12 | 17/02/2020 | 2019/20 | 22/02/2021 | 2020/21 | 21/02/2022 | 2021/22 |
48 | 12 | 24/02/2020 | 2019/20 | 01/03/2021 | 2020/21 | 28/02/2022 | 2021/22 |
49 | 13 | 02/03/2020 | 2019/20 | 08/03/2021 | 2020/21 | 07/03/2022 | 2021/22 |
50 | 13 | 09/03/2020 | 2019/20 | 15/03/2021 | 2020/21 | 14/03/2022 | 2021/22 |
51 | 13 | 16/03/2020 | 2019/20 | 22/03/2021 | 2020/21 | 21/03/2022 | 2021/22 |
52 | 13 | 23/03/2020 | 2019/20 | 29/03/2021 | 2020/21 | 28/03/2022 | 2021/22 |
30/03/2020 | 2019/20 | ||||||
HI @billyrich83,
AFAIK, power bi 'enter data' feature support adding more than 53 row of records. So I think this should be a selection range issue, you haven't picked the full range of cells from your worksheet.
In my opinion, I'd like to suggest using keyboard arrows to choose the data ranges and test again to enter data.
BTW, do any special styles exist on the worksheet around row number 53 that may affect the copy/paste operations?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |