Month Granularity Date Table

[toddpbi]

Hi everyone,

Currently I have data on a month granularity and I am trying to create a date table. I feel like it would be unnecessary to create a date table on a daily granularity considering my budget data is only a monthly level and will be compared with monthly cost data.

I would hope to have a table that looks something like this:

DateID Year Month
201701 2017 01
201702 2017 02

I know that this is quite easy to create and I am happy to use Custom Time intelligence to generate my measures. Is there a possible way to update this table without manually inputting extra rows? I.e. A dynamic date table that changes without interaction? I heard you can trick Power BI into thinking that data is on a daily granularity by using the last day of the month (i.e. 30/01/2017, 28/02/2017) so that the date table is on a month granularity. Does anyone know of the best way to go about this either through DAX or Power Query?

Let me know, thanks for any advice 🙂
Cheers.

[Ashish_Mathur]

Hi,

I would advise you to have day wise data in your Calendar Table even if you do not want it day wise.  With the Calendar Table having daily entries, you will be able to use the Time Intelligence functions.

[toddpbi]

Thank you.

It is still possible to write Time Intelligence Functions without Time Intelligence Syntax, no?

Regards,

[BILASolution]

@toddpbi

Try this, now you can apply TIme intelligence

https://1drv.ms/f/s!Avkkqin2drR5b7vnJOImh-dvblM

[BILASolution]

Hi @toddpbi

I think, The next PBIX is what you are looking for...

https://1drv.ms/f/s!Avkkqin2drR5b7vnJOImh-dvblM

It contains a dynamic calendar table in monthly granularity

Regards

BILASolution

[toddpbi]

Thank you very much. This is exactly what I was after.

Does the model need to be refreshed to update the date formatted column?

Best,

[BILASolution]

@toddpbi

If the data source changed, the answer is "Yes" , it needs to be refreshed pressing the "Refresh" button. It's the only to do

BILASolution