cancel
Showing results for
Did you mean:
Frequent Visitor

## Filtering with Date Diff

Hi all,

I'm hoping some can help me with this since i've been having a hard time figuring it out. Basically I have a table that contains a few columns and i'm looking to get some sort of date difference by filtering it base on specific value of two columns.

Example:

if the tostring column value is "network-l2" and the fromstring value is also "network-l2" then calculate the Created date difference. so the ouput should be the time time difference of "9/14 - 2:42am" - "9/14 9:54am" = 9hours + difference.  Hope that make sense. I would like this to be a new column if possible. Key Columnfromstring to tostring vlaues

1 ACCEPTED SOLUTION Super User III

Place this emasure in a card visual. IT will give you the difference in hours. You can then convert it to minutes, etc.

``````Measure =
VAR val2_ =
CALCULATE ( DISTINCT ( Table1[Created] ), Table1[fromString] = "Network-l2" )
VAR val1_ =
CALCULATE ( DISTINCT ( Table1[Created] ), Table1[toString] = "Network-l2" )
RETURN
( val2_ - val1_ ) * 24
``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 5 REPLIES 5 Super User III

What do you need exactly? A measure for only "network-l2" or will you need it for more to-FromString pairs?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers Frequent Visitor

for now just Network-l2. Super User III

Place this emasure in a card visual. IT will give you the difference in hours. You can then convert it to minutes, etc.

``````Measure =
VAR val2_ =
CALCULATE ( DISTINCT ( Table1[Created] ), Table1[fromString] = "Network-l2" )
VAR val1_ =
CALCULATE ( DISTINCT ( Table1[Created] ), Table1[toString] = "Network-l2" )
RETURN
( val2_ - val1_ ) * 24
``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers Frequent Visitor

After making some minor tweaks I was able to get the data that I need. Thank you.

RETURN
(datediff(val2_,val1_,MINUTE))/60
Frequent Visitor

almost there, the output is not correct. The value should be the difference between the two created bottom dates. 5:10am - 7:19 = 2hour etc.    