Forum Discussion

toddpbi's avatar
toddpbi
Helper II
8 years ago

Month Granularity Date Table

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.
Need Help

6 Replies

    • toddpbi's avatar
      toddpbi
      Helper II
      8 years ago

      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's avatar
        BILASolution
        Solution Specialist
        8 years ago

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.