cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
billyrich83
Regular Visitor

Custom Calendar

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 😁

 

Screenshot 2022-01-20 094717.pngScreenshot 2022-01-20 095343.png

1 ACCEPTED SOLUTION

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

7 REPLIES 7
Ville
Helper I
Helper I

I would add a column for Period in the Calendar table

I have a complete calendar table with 1 row per day.

Ville_0-1648457107347.png

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
Microsoft
Microsoft

Please see if this article is helpful.

445 Calendar with 53-Week Years – Hoosier BI

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Greg_Deckler
Super User
Super User

@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

 

 

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi Greg, 

 

Thanks for your response whilst I was waiting I was having a play about and have got this far in PowerBi. 

Screenshot 2022-01-20 113635.png

 

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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 NoPeriodPeriod Start DateYear 19/20Period Start DateYear 20/21Period Start DateYear 21/22
1101/04/20192019/2006/04/20202020/2105/04/20212021/22
2108/04/20192019/2013/04/20202020/2112/04/20212021/22
3115/04/20192019/2020/04/20202020/2119/04/20212021/22
4122/04/20192019/2027/04/20202020/2126/04/20212021/22
5229/04/20192019/2004/05/20202020/2103/05/20212021/22
6206/05/20192019/2011/05/20202020/2110/05/20212021/22
7213/05/20192019/2018/05/20202020/2117/05/20212021/22
8220/05/20192019/2025/05/20202020/2124/05/20212021/22
9327/05/20192019/2001/06/20202020/2131/05/20212021/22
10303/06/20192019/2008/06/20202020/2107/06/20212021/22
11310/06/20192019/2015/06/20202020/2114/06/20212021/22
12317/06/20192019/2022/06/20202020/2121/06/20212021/22
13424/06/20192019/2029/06/20202020/2128/06/20212021/22
14401/07/20192019/2006/07/20202020/2105/07/20212021/22
15408/07/20192019/2013/07/20202020/2112/07/20212021/22
16415/07/20192019/2020/07/20202020/2119/07/20212021/22
17522/07/20192019/2027/07/20202020/2126/07/20212021/22
18529/07/20192019/2003/08/20202020/2102/08/20212021/22
19505/08/20192019/2010/08/20202020/2109/08/20212021/22
20512/08/20192019/2017/08/20202020/2116/08/20212021/22
21619/08/20192019/2024/08/20202020/2123/08/20212021/22
22626/08/20192019/2031/08/20202020/2130/08/20212021/22
23602/09/20192019/2007/09/20202020/2106/09/20212021/22
24609/09/20192019/2014/09/20202020/2113/09/20212021/22
25716/09/20192019/2021/09/20202020/2120/09/20212021/22
26723/09/20192019/2028/09/20202020/2127/09/20212021/22
27730/09/20192019/2005/10/20202020/2104/10/20212021/22
28707/10/20192019/2012/10/20202020/2111/10/20212021/22
29814/10/20192019/2019/10/20202020/2118/10/20212021/22
30821/10/20192019/2026/10/20202020/2125/10/20212021/22
31828/10/20192019/2002/11/20202020/2101/11/20212021/22
32804/11/20192019/2009/11/20202020/2108/11/20212021/22
33911/11/20192019/2016/11/20202020/2115/11/20212021/22
34918/11/20192019/2023/11/20202020/2122/11/20212021/22
35925/11/20192019/2030/11/20202020/2129/11/20212021/22
36902/12/20192019/2007/12/20202020/2106/12/20212021/22
371009/12/20192019/2014/12/20202020/2113/12/20212021/22
381016/12/20192019/2021/12/20202020/2120/12/20212021/22
391023/12/20192019/2028/12/20202020/2127/12/20212021/22
401030/12/20192019/2004/01/20212020/2103/01/20222021/22
411106/01/20202019/2011/01/20212020/2110/01/20222021/22
421113/01/20202019/2018/01/20212020/2117/01/20222021/22
431120/01/20202019/2025/01/20212020/2124/01/20222021/22
441127/01/20202019/2001/02/20212020/2131/01/20222021/22
451203/02/20202019/2008/02/20212020/2107/02/20222021/22
461210/02/20202019/2015/02/20212020/2114/02/20222021/22
471217/02/20202019/2022/02/20212020/2121/02/20222021/22
481224/02/20202019/2001/03/20212020/2128/02/20222021/22
491302/03/20202019/2008/03/20212020/2107/03/20222021/22
501309/03/20202019/2015/03/20212020/2114/03/20222021/22
511316/03/20202019/2022/03/20212020/2121/03/20222021/22
521323/03/20202019/2029/03/20212020/2128/03/20222021/22
  30/03/20202019/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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors