calculated status

[Anonymous]

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

[v-gizhi-msft · Accepted 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

[Tahreem24]

@Anonymous ,

As per your query so can use below Calculated Column :

Status_ = SWITCH(DATEDIFF('Table'[StartDate],'Table'[EndDate],MONTH),1,"Done",0,"Ontime","Delayed")

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[Anonymous]

how does power bi recognize wether a project/milestone is late or not.

Do i need antoher column for this?

[amitchandak]

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")