Custom Calendar

[billyrich83]

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 😁

[Anonymous]

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/

[mahoneypat]

Please see if this article is helpful.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[Greg_Deckler]

@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

[billyrich83]

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? 

[Greg_Deckler · Accepted solution]

@billyrich83 Well, you could just use an Enter data query and copy and paste from Excel instead of the fancy query transformation

[billyrich83]

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

[v-shex-msft]

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