cancel
Showing results for
Did you mean:
Highlighted
Helper II

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

## Re: DATEADD Function

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.

6 REPLIES 6
Highlighted
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

## 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

## 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://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

## 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

## Re: DATEADD Function

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.

Highlighted
Helper II

## Re: DATEADD Function

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

Announcements

#### 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

Learn about the exciting things that happened in July.

#### 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

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

Top Solution Authors
Top Kudoed Authors