Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Blake753
Helper II
Helper II

EDATE for days or weeks

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))
 
1 ACCEPTED SOLUTION

var Fdate = Ldate - 14

View solution in original post

6 REPLIES 6
daxer-almighty
Solution Sage
Solution Sage

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.

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

var Fdate = Ldate - 14

@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?

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-1...). 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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors