cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. powerbit.png

Key Columnfromstring to tostring vlaues

1 ACCEPTED SOLUTION

@ddurosier 

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 

 

SU18_powerbi_badge

View solution in original post

5 REPLIES 5
Super User III
Super User III

Hi @ddurosier 

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 

 

SU18_powerbi_badge

Frequent Visitor

for now just Network-l2.

@ddurosier 

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 

 

SU18_powerbi_badge

View solution in original post

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.

 

Capture.JPGCapture2.JPG

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors