Time in a certain period, figuring out if is day or night

[sirros_iot]

Hello all

I'm trying to find out if one data was sent to me in the night or in the day. 

OBS: I think that I can't use calculated columns, the data is coming from a device(arduino) and I'm getting the data from the Power BI online dashbord. 

This is what I'm trying to do:

               Here I transform the timestamp into time:

               .UTCTime =
                          VAR UnixDays = [.ts] / (60*60*24)

                          RETURN
                          ((DATEVALUE("1/1/1970") + UnixDays))

               And here I'm working with that time:

               .Night =
               VAR X = IF( TIME(06;00;00) <= [.UTCTime] && [.UTCTime] < TIME(20;00;00)
               ; 1 ; 0)

              RETURN

              X

If I drop the [.UTCTime] in the table I get an error I think because of being a measure.

All my [.Night] column is returning only 0 or 1. What should I do? Any idea   

Thanks

[v-yuezhe-msft]

@sirros_iot,

The max valid date in Power BI is 9999/12/31, thus when you use (DATEVALUE("1/1/1970") + UnixDays) to calculate UTCTime, the max value of UnixDays is 2932896.

I am not sure the value of your [.ts] measure, but if its value exceeds 2932896 * 60 * 60 *24, you will get error when drop the [.UTCTime] measure to table.

Regards,
Lydia

[sirros_iot]

Hello @v-yuezhe-msft

I have this measure working but only if I select a row.

Do you think I can put it in the column?

[v-yuezhe-msft]

@sirros_iot,

Could you please share the PBIX file to me? You can send the PBIX file via Private Message.

The issue is caused by that the value of [.ts] measure exceeds 2932896 * (60*60*24), but not related to that measure or column you put the formula in .

Regards,
Lydia

[sirros_iot]

Any idea, I don't know even if it is possible kk, thanks