Generateseries: unexpected behaviour

[ivostammis]

I am building a maintable containing a long series of points in time. This maintable looks up / checks values from other tables which are not continuous (so containing gaps in time). To populate this table I use this query:

Maintable = GENERATE(SELECTCOLUMNS(Tagnames, "Colonne", Tagnames[Colonne], "Machine", Tagnames[Machine]), GENERATESERIES(DATEVALUE("15-06-2020 06:00:00"),DATEVALUE("22-06-2020 06:00:00"),TIME(0,0,10)))

Now the GENERATESERIES is throwing a weird result: I add 10 seconds to the next step by TIME(0,0,10) (so expect to see only times ending on :00, :10, :20 etc). However after a while the time ends on :09, :19 et! This makes my whole model not working anymore, as I also have data in the other tables on the every-10 seconds schedule.

After 9420 rows this event occurs for one machine, for the other machine after 5418 rows  (see the picture below).

Anyone an idea why this happens? At first I had 1/24/360 as timestep in GENERATESERIES , so I thought it had to do with floating point values, but now I am using TIME(0,0,10) and the issue is still here. 

 

[sturlaws · Accepted solution]

Hi @ivostammis,

I tried different variations of your table, and I experience the same issue as you. And I can't get it to work properly. Funny thing is that when I create new calculated column like this:

Date time column = DATEVALUE([Value])+TIMEVALUE([Value])

it returns the correct values. So that is one possible workaround. Another workaround would be to create the datetime-list with Power Query. Using Power Query is usually beneficial for how Power BI treats your table. Here is a recipe for how to create the table in power query:
http://datacornering.com/how-to-generate-date-and-time-interval-list-using-power-query/

Cheers,
Sturla 

[ivostammis]

Thanks! I ended up generating the table in powerquery, as DAX was already slowing my model down a lot I hope this also contributes to refresh speed 🙂

[lbendlin]

i think your floating point hunch is still correct.

Instead of cumulatively adding ten seconds, I would use a fixed datetime starting point and calculate the offsets from there. That way you can avoid the drift.