cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adnanzakir
Helper III
Helper III

DATEADD Function

Hi All. Got a curly one here. I am working with a table which has placements. The data is from 2015, In order to calculate the conversion rate, I need to select records from 2015 uptto 60 days prior from today (which is going to rolling on daily basis) .

I have been using a calculate formula for conversion rate (which is simply placements / Claims) with  dateadd function (date, -2, months). The formula works fine when I view this on a card visual but when I pull it on a table against state, site or any other field from the same table, it throws an error "DATEADD expects a contigous selection when date column is not unique, has gaps or it contains time portion". 

 

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @adnanzakir ,

 

You may create measure like DAX below.

 

Measure= CALCULATE(<claim ID / Placements measure or coulmn>,FILTER(ALLSELECTED(Sales),Sales[Date]<=DATE(YEAR(TODAY()), MONTH(TODAY())-2, DAY(TODAY()))))

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

Hi @adnanzakir ,

 

You may create measure like DAX below.

 

Measure= CALCULATE(<claim ID / Placements measure or coulmn>,FILTER(ALLSELECTED(Sales),Sales[Date]<=DATE(YEAR(TODAY()), MONTH(TODAY())-2, DAY(TODAY()))))

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi Amy. This formula worked a treat. Thank you so much for your help. 🙂

amitchandak
Super User IV
Super User IV

Dateadd needs continuous dates. That is why we recommend having a date table. There is an alternate

 

New date=date(year([Date])-1,month([Date])-1,day([Date])-1)

This mean new date is one year, one month and one day behind

 

New date=date(year([Date])-1,month([Date]),day([Date]))

One year behind

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks Amit. 

Tried this formula and came up with the following error: 

"An argument of function 'DATE' has the wrong data type or the result is too large or too small."

Basically what I am trying to achieve is for my conversion rate, select everything from 2015 up until 2 months from today. 

So current month being April, select all records from 2015 - February 2020. 

My formula would be something like: 

Conversion Rate = CALCULATE (claim ID / Placements) where placement date is up until latest_placement_date - 2 months. 

danno
Resolver V
Resolver V

Hi, are you using a date  or calendar table for your dates (i.e a single table with continuous dates)?  If not then you will need to remodel.  here is an example of a date table created using DAX; https://www.sqlbi.com/tools/dax-date-template/ 

here is a more basic version : https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Alberto and Marco have lots of good tutorials on this. 

Hi Danno. Thanks for the reply. I am using a date table. So basically it is a table with records showing the placements achieved on a specific date. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors