Forum Discussion

SimonSeez's avatar
SimonSeez
Helper III
5 years ago

DatesAdd Function with Custom Date - Help Needed

Hello,  I am using this custom date table and I need to perform some MTD, QTD and YTD calculations. I am using DatesADD function and it just does not seem to work. The result always gives the sum of...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
amitchandak's avatar
amitchandak
Super User
5 years ago

SimonSeez , in case that does not help. Also, the useful information provided by other super users/users does not help. then

Please share a sample pbix after removing sensitive data.

SimonSeez's avatar
SimonSeez
Helper III
5 years ago

Hello amitchandak 

 

Educative video as expected. I subscribed to the channel as well. I have applied everything you said and I noticed you used a DatesYTD before you used the DatesAdd. I hadn't seen this used before so I tried it 

 

This was my previous measure - 

Calculate([Total Revenue], DateAdd('Date Table' [Date], -1, Quarter)) 

 

I modified it to seem like yours 

Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Quarter))). Strangely this gave me a blank result 

 

I then remodified to this 

Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Year))) and strangely it gave me the correct result. I couldn't make sense of my the measure worked so I have some doubts. 

 

Any ideas why? 

 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    SimonSeez , Thanks for subscribing.

     

    Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Quarter)))  means last qtr. That is blank means no data in last qtr but data in last year same qtr.

     

    That sound like strange.

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • SimonSeez's avatar
      SimonSeez
      Helper III
      5 years ago

      One quick question before I share data. 

       

      I modified the measure to use the date that is in the same table as the revenue but instead of getting the total of last quarter I got the value of October 2020 

       

      Calculate([Total Revenue], DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter))).

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        SimonSeez , dateadd need continuous dates . And it return blank when it does not get it.

        refer this video, where I have shown that for a column - https://www.youtube.com/watch?v=9qiRivlBv8w

         

        The best way test is

        Calculate(Min('DWH Revenue'[Date]), DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter)))


        Calculate(Max('DWH Revenue'[Date]), DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter)))

         

        Now you know what you time intelligence function returns: min and max. It returns a set of dates.