Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ddurosier
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
AlB
Super User
Super User

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

ddurosier
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

ddurosier
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
ddurosier
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors