cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Card with states,using both time and date for calculation

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Card with states,using both time and date for calculation

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.

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Card with states,using both time and date for calculation

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?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Card with states,using both time and date for calculation

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

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Card with states,using both time and date for calculation

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.

Highlighted
Anonymous
Not applicable

Re: Card with states,using both time and date for calculation

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.

View solution in original post

Highlighted
Community Support
Community Support

Re: Card with states,using both time and date for calculation

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

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Card with states,using both time and date for calculation

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors