topic Re: EDATE for days or weeks in DAX Commands and Tips

EDATE for days or weeks

Blake753 — Mon, 05 Oct 2020 19:55:52 GMT

I am recycling a function that works perfectly minus the EDATE function. I am looking for the same thing as EDATE but I need it to operate as days or weeks (whichever one is easier) as opposed to months. Is there a simple solution for this? Here's my code for reference:

Prod Metric 2 = var Ldate = DATE(YEAR(MAX('Date Slicer'[Pay Period])), MONTH(MAX('Date Slicer'[Pay Period])), DAY(max('Date Slicer'[Pay Period])))

var Fdate = EDATE(Ldate,-6)

var sumUCC = [Productivity]

RETURN

IF(min(Dates[Date_Id])<Fdate, blank(), IF(min(Dates[Date_Id])>Ldate,blank(),sumUCC))

Re: EDATE for days or weeks

daxer-almighty — Mon, 05 Oct 2020 20:44:15 GMT

IF you have a date and you add integers (positive and negative) to it, the addition will return the day that many days after or before the date.

Re: EDATE for days or weeks

Blake753 — Mon, 05 Oct 2020 20:47:51 GMT

daxer-almighty could you put that solution into my code? Let's say I want to return 2 weeks or 14 days.

Re: EDATE for days or weeks

daxer-almighty — Mon, 05 Oct 2020 20:50:05 GMT

var Fdate = Ldate - 14

Re: EDATE for days or weeks

Blake753 — Mon, 05 Oct 2020 21:11:28 GMT

daxer-almighty Thank you that seems to work perfectly. I do have a quick follow up question: This works in a matrix format but I am trying to disply this info in a KPI visual as well. The KPI visual is coming up blank, do you have any suggestions?

Re: EDATE for days or weeks

Anonymous — Tue, 06 Oct 2020 10:42:23 GMT

Well, you should analyze your measure that you use in your visuals. The answer to your question is hidden in the logic of the measure. When you're dropping a measure onto a KPI visual, you should be aware of what data is visible to the formula. In other words, which data is filtered out and which data stays. If you understand this, you'll know why your formulas return what they return.

Re: EDATE for days or weeks

Anonymous — Sat, 24 Jun 2023 16:37:11 GMT

Hey I know it's a bit late but the KPI visual usually has a color column. And you shouldn't modify that column. For example, I have this Gann chart for task planning called "Natural Green Construction Schedule" (source: https://www.wps.com/academy/guide-to-construction-scope-of-work-templates-in-excel-quick-tutorials-1865947/). You shouldn't modify the type column because then KPI visual will have no color input and will output white color. Add a column to the right of that column if you need to add more content.