Forum Discussion

NeilL's avatar
NeilL
Frequent Visitor
6 years ago
Solved

Frustrated With Time Intelligence

Greetings,
I have a seemingly simple need but cannot seem to figure it out and have been racking my brain for days - DAX and I are not getting along.

I'm connected to my data from Dynamics365. Using the opportunities[est...date] field I need to add a duration (months).

First, I take the date and perform a STARTOFMONTH() calculation then add 2 months and store in a measure. I've got this.

[est...date] = 1/10/2020

mthlyRevBegin = STARTOFMONTH(DATEADD(opportunities[est...date].[Date],2,MONTH))

mthlyRevBegin = 3/1/2020

 

Here's my issue. I need to add 10 months to the [est...date] and then subtract 1 day to capture 10 whole months. PowerBI doesn't seem to let me nest DATEADD() or other functions nor can I use a measure within it.

 

Currently I have tried each of these:

mthlyRevEnd = EDATE([mthlyRevBegin],max(opportunities[kbdg_estdur])) ... gives 1/1/20201
mthlyRevEnd = ENDOFMONTH(DATEADD(opportunities[estimatedclosedate].[Date],10,MONTH)) ... gives the error below.

My end result for this scenario should be mthlyRevEnd = 12/31/2020.

 

What am I missing? Any help is appreciated.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi NeilL ，

     

    First create a calendar table.(Be sure the table contains enough dates you need for calculation)

    Modify the measure to below:

     

    mthlyRevEnd = ENDOFMONTH(DATEADD(calendar table[estimatedclosedate],10,MONTH))

     

    Dont use .date for calculation,and be sure that your calculation dont exceed your calendar date.

    Here is a blog about time intelligence,hope it would help.

     

     

    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

3 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    NeilL 

     

    Are you trying to get end of year(ENDOFYEAR) date? Is it a measure or calculated column?

    • NeilL's avatar
      NeilL
      Frequent Visitor
      6 years ago

      VasTg 
      Thanks for the quick reply. I am not trying to get the end of year, only the end of the month; it just so happens the example I provided in the initial post falls on 12/31/2020.

       

      Better examples of dates I might encounter:

      Est...date = 2/12/2020

      Duration = 6 months

      mthlyRevBegin = 4/1/2020

      mthlyRevEnd = 9/30/2020

       

      Est...date = 3/14/2020

      Duration = 14 months

      mthlyRevBegin = 5/1/2020

      mthlyRevEnd = 6/30/2021

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi NeilL ，

         

        First create a calendar table.(Be sure the table contains enough dates you need for calculation)

        Modify the measure to below:

         

        mthlyRevEnd = ENDOFMONTH(DATEADD(calendar table[estimatedclosedate],10,MONTH))

         

        Dont use .date for calculation,and be sure that your calculation dont exceed your calendar date.

        Here is a blog about time intelligence,hope it would help.

         

         

        Best Regards,
        Kelly
         
        Did I answer your question? Mark my post as a solution!