Creating date tables using weekly level data

[Mathangi_S]

Hi All,

I am working with weekly level data of sales and require help on creating date table using this data. The important point to be nited is that I need my 1st April of every year to be the first week every year. Assuming that I have my weeks and years information, is there a way in power bi to create quarter, half year and full year columns in a separate table to be liked to me sles data so that I can compute year to week salees, week on week comparison etc measures?

My data looks like this:

It woud be great if omebody could help me out with this asap!

Warm Regards,

Mathangi Sridhar

[MattAllington]

You can do this in Excel or Power Query.  Calendar tables are quite complex as everyone has a different rule.  Your rule is that the year starts on 1 April.  That is quite a complex thing to do given there are 52 complete weeks + 1 day in every normal year, and +2 days in a leap year.  

I suggest you use Excel and build your table manually.  This is a great way to build a calendar table as you have total control

[Mathangi_S]

Hi Matt,

I have tried to populate quarter, half-year and financial year values using week numbers for my sales dataset but there is an issuing while filtering these values in the charts. Power BI is unable to recognize these as time periods and hence not taking these fields within the time period hierarchy. 

I have even concatenating these week or quarter values with the year like Q12013, Q22014 etc...but it is still not working.

Could you suggest a way to implement this?

[Mi2n]

Only Date values will be recognized as Time period by Power BI automatically. You will have to create your own hierarchy if you are using text values for Quarter or Week.

In the UI, right click on any of the field and select "New Hierarchy". Now you have a new hierarchy (which can be renamed). You can right click on other fields to be added to this hierarchy and select "Add to *** hierarchy"