Connecting a lookup table to calendar table?

[Anonymous]

Hi,

 I've been wondering what's the best way to do this for a while now.

My current scenario is, that i'm creating a simple report, that show how many Items we have created in a given month grouped by categories (New item, Standard Item, Numerical Item).

The Item table is of course a lookup table, and has a CreatedDateTime column. In order to have it grouped by month you either need to:

1. Connect it to the Calendar-Table (I'm guessing through the use of inactive-relationship + USERELATIONSHIP)
2. Create various columns like Year, month, week, yearmonth on the Item table itself.

Which is the better approach?

[v-easonf-msft · Accepted solution]

Hi ， @Anonymous 

It depends on personal habits, I habitually create calendars table. Especially when dealing with some non-contiguous dates in the data table or using some time intelligence functions .

Often when using Microsoft Power BI, you will find that you need a calendar or date reference for your data to organize it by months or weeks, or even the day of the week. If your data has some date information in it but not the specifics you need, building a DAX date table in Power BI can help.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , Not very clean, you need to use userelation when you have more than one date. You can refer to these examples or that.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Date table

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.