cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: DATEADD Function

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
Highlighted
Resolver IV
Resolver IV

Re: DATEADD Function

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. 

Highlighted
Helper II
Helper II

Re: DATEADD Function

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. 

Highlighted
Super User IV
Super User IV

Re: DATEADD Function

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!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: DATEADD Function

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. 

Highlighted
Community Support
Community Support

Re: DATEADD Function

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

Highlighted
Helper II
Helper II

Re: DATEADD Function

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors