Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Getting a measure to work with two tables

Hi Everybody,   I wondered if anybody could help with a problem I'm having writing a measure. I have data representing the start and end times of tasks. These are datetimes with 1 minute granulari...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Add Hours to your datetime table and plot that on axis

 

Date hour = Datevalue([Datetime]) +time(Hour([Datetime]),0,0)

 

Change measure like

 

PlannedDemand =

CALCULATE ( lastnonblankvalue('DemandDT'[DateTime], COUNTROWS( 'Order Data' )),
FILTER (
'Order Data',
( [Service begin]<= MAX ( 'DemandDT'[DateTime])
&& [Service end] > MAX ( 'DemandDT'[DateTime]) )
)
)+0

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak thank you for your help.

I added a date column, created the measure and plotted it on a date axis as you suggested. It seems to just take the XX:59 minute from each hour, rather than summing all of the minutes in each hour.

I've attached a sample file to show:

Sample PIBX

 

The top chart shows is the original with one minute granularity, and the lower one is with your proposed code.

I wondered how to get the measure to sum each minute in the hour.

I also wodered whether it would be better to calculate how many minutes of each task fall within each hour, and sum those. As a proof of concept I added a calculated column to the TaskData table which does this for each row, and for a hardcoded time (08:00-09:00). I wondered if it would be possible to convert this into a measure which I could plot on an hour axis?

 

Many thanks