cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sirros_iot Member
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   

 

Captura de Tela (6).pngThanks

4 REPLIES 4
sirros_iot Member
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 v-yuezhe-msft
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.
sirros_iot Member
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?

 

Captura de Tela (7).png

Moderator v-yuezhe-msft
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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors