Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Approval Date | Forecast Date | Actual |
11/23/2019 | 12/23/2019 | |
9/1/2019 | 10/15/2019 | |
10/17/2019 | 10/17/2019 | |
10/11/2019 | 12/2/2019 | 10/14/2019 |
Solved! Go to Solution.
You could create a new column with this formula.
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.
You could create a new column with this formula.
@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 name | Construction Start Time | Construction End Time |
A | Behind | Late Finish |
B | On Time | On Time |
You would have to split the Dax into two columns as below. But what is the data model and where is project attribute?
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.
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.
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.
This is really helpful. Thank you.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |