Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody,
is there a possibility of an automatism?
the status is currently assigned manually. Goal should be that the "status" is calculated automatically.
example database
current
target state
Solved! Go to Solution.
Hi,
You can try to create calculated columns like this:
Status =
SWITCH (
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ),
0, "done",
1, "on time",
"delayed"
)
Status Details =
IF (
'Table'[Status] = "delayed",
SWITCH (
TRUE,
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 2
&& DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) < 4, "delayed >= 1 month",
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 4
&& DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) < 7, "delayed >= 3 month",
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 7, "delayed >= 6 month"
),
'Table'[Status]
)
Anytime the data changed, it will automatically reflect on Power BI Desktop visuals, the result shows:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
You can try to create calculated columns like this:
Status =
SWITCH (
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ),
0, "done",
1, "on time",
"delayed"
)
Status Details =
IF (
'Table'[Status] = "delayed",
SWITCH (
TRUE,
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 2
&& DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) < 4, "delayed >= 1 month",
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 4
&& DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) < 7, "delayed >= 3 month",
DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MONTH ) >= 7, "delayed >= 6 month"
),
'Table'[Status]
)
Anytime the data changed, it will automatically reflect on Power BI Desktop visuals, the result shows:
Hope this helps.
Best Regards,
Giotto Zhi
@Anonymous ,
As per your query so can use below Calculated Column :
how does power bi recognize wether a project/milestone is late or not.
Do i need antoher column for this?
You can create a calculated column:
refer :https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
example
status = if(datediff([end_date],[expected_end_date],day)>1,"Delay","Ontime")
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |