Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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

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...
Need Help
  • bherring1979's avatar
    bherring1979
    10 years ago

    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.

bherring1979's avatar
bherring1979
Frequent Visitor
10 years ago

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.

  • itchyeyeballs's avatar
    itchyeyeballs
    Impactful Individual
    10 years ago
    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's avatar
      sshweky
      Helper III
      10 years ago

      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

  • a68tbird's avatar
    a68tbird
    Resolver II
    10 years ago

    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