Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

daten 2.jpg

 

 

 

 

current

donut.jpg

 

 

 

 

 

 

 

 

 

 

 

 

target state

donut delayed.jpg

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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:

33.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

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:

33.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Tahreem24
Super User
Super User

@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.
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

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

Do i need antoher column for this?

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.