The DATEADD Function. The Best And Most Versatile Time Intelligence Function in Power BI

3 comments

The DATEADD function is by far my go to time intelligence function in DAX at the moment. Mainly because of it’s immense versatility to generate time comparisons over a range of different time frames.

This detailed video will go through all the various aspects of how to set up time comparison analysis with this function.

You can download the Power BI (pbix) file below and follow along throughout the video. Just let us know where to send the file.

Click here to download the DATEADD pbix file

In the video I go through what you can do with the DATEADD function, and also how you can quickly compare multiples of different time periods really extremely fast.

I think you’ll be amazed at how easy it is to actually do this type of analysis using DAX in Power BI.

Watch the video from the Enterprise DNA TV channel on YouTube

I’m hoping that from grabbing the download and watching through the video you’ve now got a much better understanding of how great the DATEADD function is.

There’s so much you can do with it right?

Thanks for checking it out.

Catch you next time

3 comments on “The DATEADD Function. The Best And Most Versatile Time Intelligence Function in Power BI”

  1. Hi Sam, this is great, but I have a question on how to lookup the current quarter based on today’s date and create a measure that provides the sum of total sales, which have also been billed. The billed is based on a column with Y or N value. I made a start in creating a measure for current Fiscal Quarter.

    Current Fiscal Quarter =
    LOOKUPVALUE(
    DimDates[FiscalQTR]
    ,DimDates[Date]
    ,TODAY()
    )

    But I’m unclear how to include in this a Calculate DAX measure, which only shows the current Sales amount for current quarter, which has been billed.

    Pending Billed = CALCULATE( [Total Billing],’Current SAP'[Rev-Bill Flag]=”N”, DimDates[FiscalQTR]=[Current Fiscal Quarter])

    I got the error A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expressions. This is not allowed.

    Like

    1. Fixed it already, but did not understand why my previous one did not work.

      Pending Revenue Current Quarter = CALCULATE(
      [Total Billing],
      Filter(DimDates,[FiscalQTR]=[Current Fiscal Quarter])
      )

      What would I need to add so that this Filter is not change if other quarters are selected at the page level, e..g this Measure always returns the current quarters number?

      Like

      1. Yep you’re on the right track. To finish that off to get that calculation regardless of the date selection you need to firstly remove any context that is coming for the date table. By adding ALL() around the DimDates. That removes any date context then you place it back on with the new logic inside the filter function.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s