How to create a dim_date table when the data table has only Years/Month/Weeks as whole numbers?

[NovaBI]

Hi everyone,

i formally know how to use the time intelligence in PowerBI, but I am struggeling understanding the concept of date tables in detail for a specific issue.

My original date table consists of 2 Years, 12 Months and 53 weeks per year (everything is imported as whole number) as well as the values that are to be compared (e.g. Sales, already sumed into the distinct week). I created a new dim_date table where i put in everything (days, weeks, Quarters, years,....) starting from 1/1/2019 until 12/31/2020.

How do i make the time intelligence work for example for an YoY or LYsameperiod? I tried to use relationsship's manager and combined year and year, but this seems not working properly.

Any best practice or a hint how to fix my issue?

[Greg_Deckler · Accepted solution]

When time "intelligence" fails you; and at some point it will, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[az38]

@NovaBI 

Usually, 

there is a Calendar dim table with date field

and a fact table with date field which connected as key to Calendar dim table

if you have only Year field in fact table it can lead you to many-to-many relationships

So, you can to define a business logic to relationships, or add a measures to Calendar table which will calculate YoY, for example