CODEWITHSUNDEEP
sqlpowerbi
Sivarama Krishnan
Sivarama Krishnan

Reputation: 43

How do I add a column in PowerBI that evaluates the difference of Latest End time and Earliest Start Time from a large dataset?

I am new to PowerBI. I am trying to make a report of the number of days consumed for a test to complete. There is a large fleet of tests that are run across a week and I would like to subtract the Earliest Start Time from the Latest End time, excluding Saturdays and Sundays and then display the result as a new column next to the Latest actual end as shown in the picture below.

PowerBI Screenshot

Pardon for any errors above. The data was fetched from a SQL Server using a query (if that helps). Thank you.

Upvotes: 0

Views: 72

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

Create a New Table DateTable to be your calendar table.

DateTable = CALENDARAUTO()

Add a weekday column so you can filter out weekends.

Weekday = WEEKDAY(DateTable[Date])

Now you can create a measure that counts the days between your first and last day:

DayCount = COUNTX(DateTable,
               IF(DateTable[Date]+1 > MIN(StartEnd[startTime]) &&
                  DateTable[Date]+1 < MAX(StartEnd[acutalend]) &&
                  NOT(DateTable[Weekday] IN {1,7}),
                  1, BLANK()))

The +1 are there to give you end of day rather than beginning of day.

Upvotes: 1

Wei Lin
Wei Lin

Reputation: 3791

query in sql server 

select 
  *
  --number of days 
  ,DATEDIFF (day,[Earrliest startTime], [Latest actualend]) diffday
from TestTable
--excluding Saturdays and Sundays
where datepart(weekday,[Earrliest startTime]) not in (6,7) 
  and datepart(weekday,[Latest actualend]) not in (6,7)

SQL Fiddle

Hope it help you :-)

Upvotes: 1

Related Questions