Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculated status

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

Need Help
Tips and Tricks
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      Do i need antoher column for this?

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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