Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 trea...
Need Help
Show and Tell
  • sdjensen's avatar
    sdjensen
    8 years ago

    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.

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

 

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    No only week and year are specified.
    • KeithChu's avatar
      KeithChu
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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.

         






        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.