cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powermx Frequent Visitor
Frequent Visitor

DateADD Function not returning dates correctly

My table appears to have contiguous dates.  I am trying to compare the sum of charges from my current month as of a specific date to a prior month for the same time frame.  For example, I want to compare charges from 11/1 - 11/21 to prior month 10/1 - 10/21.  My date add function is returning the sum for all dates in my prior month (10/1 - 10/31).  Not sure how to get around this.  Any help would be appreciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
powermx Frequent Visitor
Frequent Visitor

Re: DateADD Function not returning dates correctly

Thanks for your responses!  I was able to find the solution to my formula.  I was not using the ENDOFMONTH function which was causing the problem.  My formula is shown below.  It is now working appropriately, and giving me the prior month MTD charges comparable to the date in the current month I am viewing.  Eg. Nov 15 MTD charges are now comparable to Oct 15 MTD charges.  Keep in mind that this formula only produces charges as of the current month end date.  So if you had total charges for the month of September as of 9/30, it would only give you comparable charges as of 8/30 (not for the full month of August).  However, if I did have total charges as of 7/31, it would produce for the previous month, total MTD charges as of 6/30.    

PrevMTDCharges = TOTALMTD([TotalCharges],DATEADD(ENDOFMONTH('Calendar'[Date]),-1,MONTH))

View solution in original post

3 REPLIES 3
guyzoo New Member
New Member

Re: DateADD Function not returning dates correctly

Hi @powermx,

 

could you please, share your formula?

 

thanks

kcantor Super Contributor
Super Contributor

Re: DateADD Function not returning dates correctly

@powermx

You must have a seperate date table for DATEADD to work correctly. If you are pulling your dates from your fact table you will encounter errors. All Time intelligence calculations require a seperate date dimension table.

See this link for more information:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
powermx Frequent Visitor
Frequent Visitor

Re: DateADD Function not returning dates correctly

Thanks for your responses!  I was able to find the solution to my formula.  I was not using the ENDOFMONTH function which was causing the problem.  My formula is shown below.  It is now working appropriately, and giving me the prior month MTD charges comparable to the date in the current month I am viewing.  Eg. Nov 15 MTD charges are now comparable to Oct 15 MTD charges.  Keep in mind that this formula only produces charges as of the current month end date.  So if you had total charges for the month of September as of 9/30, it would only give you comparable charges as of 8/30 (not for the full month of August).  However, if I did have total charges as of 7/31, it would produce for the previous month, total MTD charges as of 6/30.    

PrevMTDCharges = TOTALMTD([TotalCharges],DATEADD(ENDOFMONTH('Calendar'[Date]),-1,MONTH))

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 430 members 4,105 guests
Please welcome our newest community members: