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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors