cancel
Showing results for
Did you mean:
Member

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

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

4 REPLIES 4
Member

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

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

Moderator

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

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

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?

Moderator

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!