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
tpowell86
Frequent Visitor

Summarise in and out transaction data

Hi,


I have been trying to find a way to find out which vessels were booked out at a certain date in time. (would have been status O on the date choosen.)


We have a sql table where all the historical transactions are stored, the table contains the vessel number, what type is the transaction (In or Out) and the date/time of that transaction, currently over 7 million transactions.


there is no link between the transactions but the vessel must be booked in before it can be booked out again.

 

example of the data is below 

tpowell86_0-1618222844765.png

 


Does anyone have any suggestions ideas? Thanks in Advance

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

You will need a DateTime table or better separate Date and Time tables (Time table has one row for the smallest time increment you want in your visuals), and split the DateTime column in your table to Date and Time columns.  You can then relate the Date column to the Date table, and same for the time column to the Time table.  From there, you just make a measure that counts the number of vessels where the max row has a type of "O".

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Employee
Employee

You will need a DateTime table or better separate Date and Time tables (Time table has one row for the smallest time increment you want in your visuals), and split the DateTime column in your table to Date and Time columns.  You can then relate the Date column to the Date table, and same for the time column to the Time table.  From there, you just make a measure that counts the number of vessels where the max row has a type of "O".

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.