cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calendar table causing problems.

Hi all,

 

I have tried different calendar tables out there, but none seem to help me with matching the data i have.

 

I basically have a table with sales data per week and year, but Power BI treat my week number as "whole numbers", and if i change the data type to "date" then it turns into some 1900s years.

 

If i keep the week number as numbers and try to match it with week number from my calendar table then it fails.

 

cap2.PNG

 

Capture.PNGcap3.PNG

here is my calendar table formula:

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2015;1;1); DATE(2021;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

 

 

 

 

Please help.

 

best,

Jakob

 

1 ACCEPTED SOLUTION

Accepted Solutions
sdjensen Senior Member
Senior Member

Re: Calendar table causing problems.

Hi @Anonymous,

 

My code is not M code (power query language) it is a DAX formula to calculate a new column in your sales table.

 

1. Go to the "Data" tab (icon looks like a table in the menu in the left side) (Don't enter "Edit Queries")

2. Go to your table with your sales transactions

3. On the "Home" tab in the top menu select "New Column" under "Calculations" (just to the left of the Publish button)

4. Insert this code in the formula field "FirstDayOfWeek = DATE([Year];1;-2) - WEEKDAY(DATE([Year];1;3)) + [Week_number]*7" (and press enter)

 

You now have a new column in your sales table the should hold the first date of the week calculated from your year and week column and you should be able to make a relationship between this column and your date column in your calendar table.

 

 

/sdjensen

View solution in original post

16 REPLIES 16

Re: Calendar table causing problems.

HI @Anonymous

 

I would create a column in your 'Matas_Weekly_Sales' table that is the actual date (use a date time) for the start, or end of each week period the transaction belongs to. 

 

Then create a relationship between this new column and the [Date] column in your 'Date' table.  Do not try to create a relationship to the [Week_number] column in your table.  It won't work and you don't need to 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Calendar table causing problems.

Hi @Phil_Seamark

 

Thank you for the reply. 

 

So you are saying that i should create a new column in the sales data file that somehow transform the year and weeknumber into a specific date within the week of that week number and year.

 

How exactly can i do this? I am completely new to Power BI.

 

cap4.PNG

Re: Calendar table causing problems.

Does your sales data have a date column that shows the day of the transaction?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Calendar table causing problems.

No only week and year are specified.
KeithChu Regular Visitor
Regular Visitor

Re: Calendar table causing problems.

So your data is already aggregated by year by week? If so, then add a calculated column in both table that concatenate [Year] and [Week] as a key and join them.

Anonymous
Not applicable

Re: Calendar table causing problems.

Hi @KeithChu

 

thanks for reply.

 

So your data is already aggregated by year by week: YES

 

Unfortunately i still get error message.

 

Please see attached pictures of what i did.

 

cap5.PNG


cap6.PNG

Now i have made 2x concatenate columns in each of the data sets and i try to link them together and then i get error message.

 

cap7.PNG

sdjensen Senior Member
Senior Member

Re: Calendar table causing problems.

In your table with sales data you could calculate the first date of the week with using the formula below (replace the column references "Year" and "Week" with the names of the columns in your table), and then connect this column to your calendar table.

 

FirstDayOfWeek = DATE([Year];1;-2) - WEEKDAY(DATE([Year];1;3)) + [Week]*7
/sdjensen
Anonymous
Not applicable

Re: Calendar table causing problems.

Hi @sdjensen

 

😞

 

I dont understand what you mean. 

 

Remember i am compelely new to Power BI so i need the full process described to get there. This is what i did:

Click "edit queries" click "transform" ribbon then select the column named "Year" in the Sales Data table and then i dont understand your instructions anymore. 

 

I should replace the column with a name in my table? what name and where in the table? and where do i use your formula?

 

 

Floriankx Established Member
Established Member

Re: Calendar table causing problems.

Your date table containes unique Dates but each week has seven days. So each week number appears seven times a year multiplied by the number of years. So week number is no unique value to create a relationship with.

 

This is why @sdjensen suggested you to add a Date to your table so you can relate this to the date column of your date table which contains unique values.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,147)