Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    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.

amitchandak's avatar
amitchandak
Super User
6 years ago

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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.