Card with states,using both time and date for calculation

[Anonymous]

I am working on card with states,I have a measure that is late = TIME(09,05,00) and this is used as a state.
What happened recently is that the late criteria is changed from 9:05 to  9:35, I should be able to see the state changing not only with the late measure but also corresponding dates should be considered for calculating the late.

Date                Time    Late  Changes

11/30/2019     9:07     Yes    Before

12/01/2019     9:36     Yes    After

12/03/2019     9:01     No     After

So when I click on 12/03,my card should not display late color,since this is updated now.
How can I achieve this in DAX?

[v-frfei-msft]

Hi @Anonymous ,

We can create a measure as below to work on it.

late = 
VAR dat =
    MAX ( 'Table'[date] )
VAR ti =
    MAX ( 'Table'[Time] )
VAR datein =
    DATE ( 2019, 12, 01 )
VAR be =
    TIME ( 09, 05, 0 )
VAR aft =
    TIME ( 09, 35, 0 )
RETURN
    IF (
        dat < datein
            && ti > be,
        "yes",
        IF ( dat >= datein && ti > aft, "yes", "no" )
    )

For more details, please check the pbix as attached.

[v-frfei-msft]

Hi @Anonymous ,

We can add the measure to the card and it will be dynamic by the selected value in the slicer.

late = 
VAR dat =
    MAX ( 'Table'[date] )
VAR ti =
    MAX ( 'Table'[Time] )
VAR datein =
    DATE ( 2019, 12, 01 )
VAR be =
    TIME ( 09, 05, 0 )
VAR aft =
    TIME ( 09, 35, 0 )
RETURN
    IF (
        dat < datein
            && ti > be,
        "yes",
        IF ( dat >= datein && ti > aft, "yes", "no" )
    )

[Anonymous]

I don't have a yes or no card,I am showing the actual time like this 09:30:05,so if I click on a filter it works but on a overall level it doesnt change states.

Also with a measure I cant use Max,I had to use the value().I hope you get my concern here. Can you please help on this

[Anonymous · Accepted solution]

t tweeked a bot and created a calculated column it works well,but if I create a measure and use it in my card with states visual,I dont get to see that option where you can enter the condition to be matched for returning appropriate action in the card visual.

This is my measure

late_12 =

VAR dat =

value( emp_data[Date - Copy] )

VAR ti =

value( emp_data[first_in] )

VAR datein =

DATE ( 2019, 04, 15 )

VAR be =

TIME ( 09, 05, 00 )

VAR aft =

TIME ( 09, 35, 00)

RETURN

IF (

dat < datein

&& ti > be,

1,

IF ( dat >= datein && ti > aft, 1, 0 )

)

this is my calcualted column,

late_12 =

VAR dat =

max ( emp_data[Date - Copy] )

VAR ti =

max ( emp_data[first_in] )

VAR datein =

DATE ( 2019, 04, 15 )

VAR be =

TIME ( 09, 05, 00 )

VAR aft =

TIME ( 09, 35, 00)

RETURN

IF (

dat < datein

&& ti > be,

1,

IF ( dat >= datein && ti > aft, 1, 0 )

)

I also dont get to see the median time value on a population level showing different state level ,when the time is say 9:35,I should be seeing a red color text,where as I get to see the black color text,this is default.

How to achieve this after having done all the other things,how to achieve the state at population level.

[Greg_Deckler]

I'm not understanding what you are requesting. Are you saying that for past dates the late status should be calculated at the old time and for the new dates they should be calculated at the new time?

[Anonymous]

Exactly,till 01-05-2019,the late time was 9:05 AM,after 01-05-2019,the late time is 9:35 AM.I should have a single measure that I could link with the card with states visual.I hope you get my question now.