Calculating the measure total for a date

[Anonymous]

Hello All,

I am a new user to PowerBI and seem to be stuck in a jam. My data is structured as below.

What I want to do is calculate the idle time for a given machine on a particular date. A little background on the data structure

Date Received Column is a type Date/Time in PowerBI, Date Received Copy is a duplicate of the  Date received column and has been changed type to be date.

What I want to achieve is for a particular date sum up the run time and subtract it from 86400 to get the idle time which ive calculated as a measure  Idle Time = 86400 - sum('TableName'[Run Time] 

Ive tried doing it but there seems to be errors. When i try to make a bar chart with the idle time calculated measure and the date receved copy column, the dates are split and show up with times as well as seen below. Please help!

[AlB]

Hi @Anonymous

Use the column that you formatted as type Date for the x axis of your chart instead of the column that is of type Date/Time

[Anonymous]

AIB,

Thanks for the quick reply. Although I am already using the date type column and not the date/time type column 😞

[AlB]

@Anonymous

Can you share the pbix?

[AlB]

@Anonymous

ok, maybe you can share a pbix that reproduces the issue that you have but with just some dummy data. You'll have to assess if it's worth the effort. 

[Anonymous]

AIB ,

I am trying to make a mock of the data and post pbix. I seem to have run into another issue. 

Is there a way in powerBI to make an LOD similar to Tableau?

It seems my idle time formula was erroneous and it is not the true idle time. What I want to do is calculate the total of the run time for the different rows having the same date and then subtract that from 86400. Can you please advise? I think I will have to write a calculat eexpression but cant figure out how to set it up.

[AlB]

@Anonymous

If you have a slicer with the date (Date Received_Copy] and another one with the Machine (or if those act as filters on your matrix), i believe your measure should be fine.   

[Anonymous]

Sorry AIB, cant share it as it is confidential data. Ill try and change the date type again using locale to see if that makes it work