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 ...
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

     

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