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.

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