Question: Date Table Required for Date/Time functions...?

[Anonymous]

I am very new to power BI and am confused about date/time functions.  None of them seem to work (it is early Dec 2015 and I have the most recent version of power bi Desktop).  After researching older threads, it seems:

1).  that a standalone 'Master' Date table needs to be created and imported into power BI Desktop before any of these functions work..is this still correct? 

2).  If so, do you then have to join every other table with a date to this 'Master' Date table? 

3).  This 'Master' Date table would then be used in all date/time formulas for all tables?

4).  Would this 'Master' Date table need to have a start date as far back as any date in any of your other tables?  It would also have to have an end date equal to or beyond any date in any of your other tables, correct?  (so at a minimum, the end date would need to be 2015-12-10 at the time of this writing.  Should I just use an extreme date range to avoid maintenance of this date table (ex.  used 1990-01-01 as startdate and 2018-12-31 as enddate)?

BTW - I am using this formula to generate what I am calling the 'Master' Date table

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

1.)  I am also getting an error when i try to add a day as Ken Puls suggested in the comments - how do I do this?

Thanks in advance...

[bherring1979 · Accepted solution]

You're probably better off using something online like Azure Marketplace calendars. They update for you so you don't have to keep up with it.

You'll need to set up an Azure Marketplace account (free) and browse to find a calendar you like. Once you find one and either buy or get it for free, you will receive an feed key that will grant you access for refreshing and such.

Under your Get Data menu, if you click more options, you will see the Azure Marketplace option.

When prompted, add the Feed Key and it will import the master calendar for you.

Once you have this imported, all of your date functions will reference this.

Example:

Total Income MTD Last Year = CALCULATE([Total Income], DATEADD(DATESMTD('Opportunity Allocations'[Start Date]), -1, YEAR), ALL('Calendar'[Date]))

 Where 'Calendar' is the name of the calendar you imported from the Azure Marketplace.

Hope this helps.

[a68tbird]

Could you give an example of a calendar resource in the marketplace? I've gone in there and am simply searching "calendar" but I'm not really sure what I should be looking for.  All the results returned seem to be much more involved that what I'm looking for.

thanks

[itchyeyeballs]

Having a separate date table is usually best practice but the date functions should work without one. Make sure you have set date fields to datetime type not date.

If you have multiple data tables then a separate linked date table will let you analyse each table against each other and use a single slicer to filter all analysis at same time.

[sshweky]

Hi - I am having the same issue and I really need direction. Please see my statement below. I don't understand the role of the Dates table & how the PREVIOUSMONTH function knows what the current month is to compare it to?

ShpdLast30 = CALCULATE (sum(InvoiceDetail[QtyShpd]), PREVIOUSMONTH(Dates[Date]))

Thank you!!

Steven