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 IX
Super User IX

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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors