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
npombo
Helper I
Helper I

Calculating Difference in Hours Based on Timestamps Residing in the Same Column

Hello,

 

I am hoping to enlist some help in writing this particular calculated column. My data table is currently organized like this:

 

Item ID | Type of Transaction | Date

1           | Buy                          | 1/1/22 3:56 PM

1           | Sell                          | 1/2/22 11:45 AM

2           | Buy                          | 1/4/22 5:47 AM

2           | Sell                          | 1/7/22 9:27 AM

 

I am attempting to calculate the difference in hours between the buy and sell dates for each item (based on Item ID). This would be done by subtracting the buy date from the sell date, not the other way around. This needs to be done for each item that has both a buy and a sell date (not concerned with the items that don't have both a buy date and a sell date). I also do not want to include the items where the sell date is before the buy date (there are some instances of this unfortunately).

 

I appreciate any insight/help that can be provided!

8 REPLIES 8
rsbin
Super User
Super User

@npombo ,

There are probably several different ways to do this, but I will describe what I think is the easiest:

1) Use Power Query to Pivot your Type of Transaction column

Select Transaction Column, Put Date into the Values Column and select Don't Aggregate

rsbin_0-1666644270363.png

Resulting table should look like this:

rsbin_1-1666644356504.png

Then I would use DAX to create a Calculated Column:

HoursDifference = SWITCH(
                     TRUE(),
                    OR(ISBLANK( [Buy] ), ISBLANK([Sell] )), BLANK(),
                    ([Buy] - [Sell])*24 > 0, Blank(),
                     ([Buy] - [Sell])*24 )

This checks all of your conditions you stipulated:

rsbin_2-1666644472094.png

Hope this helps you on your way.

Regards,

Hi rsbn,

 

Thank you for your response. Unfortunately, the data I am working with is derived from a live dataset and I therefore don't have the ability to use Power Query. This is what I see when I go to transform data:

npombo_0-1666646902685.png

The solution would have to come in the form of calculated measures, calculated columns, and calculated tables. Please let me know if you have any other ideas!

 

@npombo ,

Please try this as a Calculated Column:

HoursDifference = 
VAR _Item = [ItemID]
VAR _BuyDate = CALCULATE( MAX( [Date] ),
                          FILTER( BuySell2, [Type of Transaction] = "Buy" &&
                                  [ItemID] = _Item ))
 VAR _SellDate = CALCULATE( MAX( [Date] ),
                          FILTER( BuySell2, [Type of Transaction] = "Sell" &&
                                  [ItemID] = _Item ))
VAR _Result = SWITCH(
                     TRUE(),
                    OR( _SellDate = 0, _BuyDate = 0 ), Blank(), 
                    ( _BuyDate - _SellDate )*24 > 0, Blank(),
                     ( _BuyDate - _SellDate )*24 )                                                           
RETURN
   _Result

ItemIDType of TransactionDateHoursDifference

1 Buy 1/1/2022 3:56:00 PM -19.8166666666511
1 Sell 1/2/2022 11:45:00 AM -19.8166666666511
2 Buy 1/4/2022 5:47:00 AM -75.6666666667443
2 Sell 1/7/2022 9:27:00 AM -75.6666666667443
3 Buy 1/8/2022 10:00:00 AM  

 

Regards,

Hi rsbin,

 

I appreciate your efforts, and can see that we are getting closer to a viable solution. I have attempted this calculated column and am receiving errors. One of which is a circular dependency error (A circular dependency was detected: Date[Column 2].). I have also attempted to create this calculated column in a new table that only contains buy and sell type of transactions. The error I get in this instance is due to no aggregations on the Item ID field (A single value for column 'Item ID' in table 'Example Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.).

 

Please advise, and thanks again for your work.

@npombo ,

I have written this as a Calculated Column, not as a Measure.  These are two different things.

Please double check that you are creating a calculated column in your data table.

 

Correct, both of these instances I've tried have been written as calculated columns, NOT as measures.

@npombo ,

If you can, please attach your sample pbix file after removing any sensitive data.

Or else, please post a small sample of your data table (including table name) and the Calculated Column(s) you have written so I can compare them to what I have provided.

@npombo ,

Understand the constraints.  Unfortunately, don't have the time now.

Suggest you re-post and ensure you add in this new fact.  You should get some help tonight, but if not, I can have another go at it in the morning.

Regards,

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.