Forum Discussion
Time Intelligence DAX
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.
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
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.
6 Replies
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
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
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.