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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bchager6
Super User
Super User

Filter one column, then return text value based upon date comparisons

Hi All:

 

Based on the dates in the table below, I need to write an expression, or expressions, to determine if a project started late or on time, and if it finished late or on time, assuming it finished. I am open to using one or two expressions, but would prefer two so I could use both in separate columns in a table. I've tried using filters and IF functions, but can't seem to get them to return the correct values or have them interact well.

 

Here are the rules:

 

If there's no date in the Actual column (if it's blank), then compare the Forecast Date to the Approval date.

If the Forecast Date is greater than the Approval Date, then it's "Behind," else it's "On Time"

If there IS a date in the Actual column (i.e. the project finished), then compare the date in the Actual column to the Approval Date column.

If the the date in the Actual column is greater than the date in the Approval Date column, then it's a "Late Finish," else it's "On time"

 

After these comparisons are done, I need to be able to count how many projects started Behind or On Time and how many projects finished Late or On Time and show that in one or two visualizations that would preferably interact well. 

Huge kudos in advance to anyone who can help me figure this one out.

 

Approval
Date
Forecast DateActual
11/23/201912/23/2019 
9/1/201910/15/2019 
10/17/201910/17/2019 
10/11/201912/2/201910/14/2019
2 ACCEPTED SOLUTIONS
VasTg
Memorable Member
Memorable Member

@bchager6 

 

You could create a new column with this formula.

 

IF(ISBLANK('Table'[Actual]),IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Behind","On Time"),IF(DATEDIFF('Table'[Actual],'Table'[Approval Date],DAY)<0,"Late Finish","On time"))
 
If it answers your questions, Accept this as a solution.
I don't mind kudos too.
Connect on LinkedIn

View solution in original post

@bchager6 

If you want the status for Construction Start time, Please modify the DAX as below. But this contradicts with your initial ask. It will be easier for us if you provide the exact input data and expected visual.

 

Construction Start Time = IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Behind","On Time")

 

 

Connect on LinkedIn

View solution in original post

7 REPLIES 7
VasTg
Memorable Member
Memorable Member

@bchager6 

 

You could create a new column with this formula.

 

IF(ISBLANK('Table'[Actual]),IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Behind","On Time"),IF(DATEDIFF('Table'[Actual],'Table'[Approval Date],DAY)<0,"Late Finish","On time"))
 
If it answers your questions, Accept this as a solution.
I don't mind kudos too.
Connect on LinkedIn

@VasTg  This calculated column produces 4 results (2 for Construction Start Time and 2 for Construction Finish Time ) that I imagine can be visualized in 2 columns in a table. Would you happen to know how I'd pull each of those 4 values in to a table as per the below example? 

 

Project nameConstruction Start TimeConstruction End Time
ABehindLate Finish
BOn TimeOn Time

@bchager6 

 

You would have to split the Dax into two columns as below. But what is the data model and where is project attribute?

 

Construction Start Time = IF(ISBLANK('Table'[Actual date]),IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Behind","On Time"),"")

 

Construction End Time = IF(ISBLANK('Table'[Actual date]),"",IF(DATEDIFF('Table'[Actual date],'Table'[Approval Date],DAY)<0,"Late Finish","On time"))
Connect on LinkedIn

The Project attribute is within the same table. The table has not been split up in to dimension and fact tables. Below is the output.

Project A has a Start Time status populated and a blank End Time. I am OK with that because it hasn't ended yet. 

Project B has a Late Finish status populated, but the Start Time is blank. I am guessing that the Start Time is blank because of how the model is (or isn't) set up, which I believe is to your point.

 

Capture2.JPG

 

@bchager6 

If you want the status for Construction Start time, Please modify the DAX as below. But this contradicts with your initial ask. It will be easier for us if you provide the exact input data and expected visual.

 

Construction Start Time = IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Behind","On Time")

 

 

Connect on LinkedIn

This looks to be working. Your time and expertise is very much appreciated. I can now see start and end statuses within the same  row in the table visualization. I added "Not Complete" to the End Time expression to handle the blanks for when a project is still in progress.

 

Start Time = IF(DATEDIFF('Table'[Forecast Date],'Table'[Approval Date],DAY)<0,"Late Start","On Time")

End Time = IF(ISBLANK('Table'[Actual Date]),"Not Complete",IF(DATEDIFF('Table'[Actual Date],
'Table'[Approval Date],DAY)<0,"Late Finish","On Time"))

This is really helpful. Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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