Time Intelligence DAX

[Anonymous]

I have data like this

So whenever my system time match to "IN" time value in the column then I want to show in Status column as "Occupied" and whenever the system time match with "OUT" time value in the column then I want to show in Status column as "Available". So, from 8:00:00 to 17:00:00 I want to show status as "Occupied" and after that onwards I want to show it as "Available". Like this I want to show it for all rows. How to do this.

[DataVitalizer · Accepted solution]

Hi @Anonymous 

You can add a calculated column to your table using this code

Status = 
VAR CurrentTime=TIME(HOUR(NOW());MINUTE(NOW());SECOND(NOW()))
Return IF(AND(CurrentTime>='Table'[IN];CurrentTime<'Table'[OUT]);"Occupied";"Available")

Did it work ? 👌Mark it as a solution to help spreading knowledge 👉A kudos would be appreciated

[davehus · Accepted solution]

Hi @Anonymous ,

In order for this to be refreshable in a browser, you will need a calculated measure as opposed to a column. Columns are refreshed with the dataset and not dynamically.

Try the code below:

Current Time = Time(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
Status = IF(AND([Current Time]>=FIRSTNONBLANK(Test[In],1),[Current Time]<=FIRSTNONBLANK(Test[Out],1)),"Occupied", "Available")

This can be refreshed using the refresh button in broswer window or on report load.

Ignore the 1 hour current time difference in my example as it's a setting in the service. 

[Anonymous]

Upto to IN 14:00:00 and OUT 23:00:00 it working fine. But when it coming for IN 15:00:00 and OUT 24:00:00 onwords its not working.

[DataVitalizer · Accepted solution]

Hi @Anonymous 

You can add a calculated column to your table using this code

Status = 
VAR CurrentTime=TIME(HOUR(NOW());MINUTE(NOW());SECOND(NOW()))
Return IF(AND(CurrentTime>='Table'[IN];CurrentTime<'Table'[OUT]);"Occupied";"Available")

Did it work ? 👌Mark it as a solution to help spreading knowledge 👉A kudos would be appreciated

[Anonymous]

Upto to IN 14:00:00 and OUT 23:00:00 it working fine. But when it coming for IN 15:00:00 and OUT 24:00:00 onwords its not working.

[davehus]

Hi @Anonymous , That looks like a calculated column to me and not a calculated measure, can you clarify? Columns only updated once at model refresh, they don't update dynamically where as measures have that ability.

D

[Anonymous]

First I tried with calculated measure it was not working with calculated measure so then I tried with calculated column.