Forum Discussion
Time Intelligence in Power BI
Hello All,
We are devloping a report in Power BI which is having Monthly data with single table in Power BI. Lowest granularity is Monthly. We pull data from Excel. We pull month data as "Jun-23" or "Jul-23" which means June 2023 and July 2023. Date hierarchy is also created automatically in table when we pull data. Our question(s) are
1) Do we need separate date dimension in this scanario.
2) If we are going to add one more table (Month level only). Do we need any date dimension then?
3) What is the disadvantages if we dont use date dimension in case we have single table for everything.
Thanks,
PBI V2
Hi Anonymous
I have a similar data source which groups the transactions by period in which they were posted in. The transaction dates don't matter. So to answer your questions:
1) Do we need separate date dimension in this scanario.
I would still use a separate dates table although this will not necessarily include a contiguous columns of date.
Sample below (You may use month end dates)Your date should have Month column similar to above to be able to relate the two tables.
2) If we are going to add one more table (Month level only). Do we need any date dimension then?
If you won't be doing time intelligence at transaction dates level (same as my data source) then the answer for the first question should suffice.
3) What is the disadvantages if we dont use date dimension in case we have single table for everything.
A dates table simplifies time intelligence calculation. I've learned it the hard way.
This is from https://www.burningsuit.co.uk/blog/why-do-i-need-date-table-my-data-model
The starting point to using time intelligence functions is the creation of a date table, or more precisely a date dimension, as I prefer to call it. This is because most time intelligence functions are designed to work with a date table as an integral part of the data model. You may feel your data model doesn’t require a date dimension, but you’ll struggle to create the date-based calculations you need, and you certainly won’t be able to reap the benefits of time intelligence measures.Note: if a function requires contiguous dates, you will need a complete set of dates in your dates table instead of just the first or last day of the month.
1 Reply
Hi Anonymous
I have a similar data source which groups the transactions by period in which they were posted in. The transaction dates don't matter. So to answer your questions:
1) Do we need separate date dimension in this scanario.
I would still use a separate dates table although this will not necessarily include a contiguous columns of date.
Sample below (You may use month end dates)Your date should have Month column similar to above to be able to relate the two tables.
2) If we are going to add one more table (Month level only). Do we need any date dimension then?
If you won't be doing time intelligence at transaction dates level (same as my data source) then the answer for the first question should suffice.
3) What is the disadvantages if we dont use date dimension in case we have single table for everything.
A dates table simplifies time intelligence calculation. I've learned it the hard way.
This is from https://www.burningsuit.co.uk/blog/why-do-i-need-date-table-my-data-model
The starting point to using time intelligence functions is the creation of a date table, or more precisely a date dimension, as I prefer to call it. This is because most time intelligence functions are designed to work with a date table as an integral part of the data model. You may feel your data model doesn’t require a date dimension, but you’ll struggle to create the date-based calculations you need, and you certainly won’t be able to reap the benefits of time intelligence measures.Note: if a function requires contiguous dates, you will need a complete set of dates in your dates table instead of just the first or last day of the month.