Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PhotoBiker
Advocate I
Advocate I

Problem with "DateAdd" as measure

 Hi,

 

I'm trying to add 6 months to a list of dates.

 

I'm connecting Power BI "live" to an SSAS data model, therefore I can't add calculated columns (which otherwise would be the simplest solution, I guess). The table below shows a list of dates, each row is a particular service provided to a client, the "Service Referral Date" and "Service Start Date" are pulled from the "Service" fact table.

 

Since I can't add calculated columns, I defined two measures (which I hoped would just do the same) as follows:

 

Add To Referral Date = dateadd('FACT Services'[Service Referral Date],6,MONTH)

Add To Start Date = dateadd('FACT Services'[Service Start Date],6,MONTH)

 

As shown in the table, this approach works, but not for all dates.

 

DateAddIssue.png

 

 

I did some research I and found that DateAdd only works with contiguous dates, so somehow I'm suspecting that the problem is caused by "missing dates" in the fact table because the problem seems to exist only when the calculated date is bigger than today, and such dates do not exist in the fact table. I'm not quite sure how to approach that.

 

Anyway, all I'm trying to do is to add x number of months to a given date. Would there be a more straight forward solution than "DateAdd"?

 

(And if a more "nerdy" solution is required: The data model contains for both dates an independently linked "Date" table which contains all required dates, i. e. the Referral Date is linked to table "Referral Dates", and the Start Date is linked to table "Start Dates", but I'm not sure how to reference the dates there from within the measure).

 

Thanks in advance for any help here.

 

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @PhotoBiker

 

Have you tried the EDATE function?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I had this problem too, besides using the EDATE function you can also use the DATEADD function but you need to append .[Date] to the column containing the date.

 

Add To Referral Date = dateadd('FACT Services'[Service Referral Date].[Date],6,MONTH)

Add To Start Date = dateadd('FACT Services'[Service Start Date].[Date],6,MONTH)

 

There are other date related options to append, the complete list is

[Date], [Day], [Month], [MonthNo], [Quarter], [QuarterNo], [Year]

 

These options aren't in the MS DATEADD documentation.

[UPDATE:
Since posting the above reply I have learned that DATEADD generates a table, hence why using this to populate a column failed or required hacks. The correct function to use is EDATE which returns a date.
https://docs.microsoft.com/en-us/dax/edate-function-dax 
https://dax.guide/edate/  ]


Phil_Seamark
Employee
Employee

Hi @PhotoBiker

 

Have you tried the EDATE function?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

Thank you very much for putting me on the right track. I didn't know about edate, and it works perfectly, I just needed the following "workaround":

 

Edate didn't allow me to reference to any of the existing dates (Start, Referral), it only allowed me to reference another measure. So I set up a measure "MaxReferralDate" and "MaxStartDate", and then I added 6 months via edate(MaxReferralDate, 6). 

 

(I have a sense that it might be a bit of a "brute force" approach, but as I'm digging deeper into filter and row context, maybe I'll find an (even) more elegant solution.)

 

But this absolutely works for my purpose, so thank you very much!

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.