Forum Discussion
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 😁
billyrich83 Well, you could just use an Enter data query and copy and paste from Excel instead of the fancy query transformation
7 Replies
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 __PeriodHi 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
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/
