powerbidaxssasdata-analysispowerbi-desktop

GENERATESERIES function for calendar with hour interval not working as intended

I am trying to generate a calendar with hourly intervals based on my fact table.

Logic: Get the earliest and latest dates in the fact and create the calendar based on those years.

Formula used:

VAR minDate = MIN(Monitoring[Usage Date])
VAR maxDate = MAX(Monitoring[Usage Date])

VAR startDate = DATE(YEAR(minDate), 1, 1)
VAR endDate =  DATE(YEAR(maxDate), 12, 31)

RETURN GENERATESERIES(startDate, endDate, 1/24)

Problem: At some point, the time is being shifted form XX:00:00 to XX:59:59.

Is there something I am missing in the logic, code, or anything else?

Data Snapshot

Solution

  • I see the same behaviour. I suspect it is because of floating point arithmetic from 1/24.

    Incidentally, this is not how you generate a table like this. You should have separate data and time tables and time should be just 24 hours otherwise the high cardinality will kill performance.

    Edit

    As suspected, it is floating point arithmetic and this works for a single year:

    Test = 
VAR minDate = DATE(2023, 1,1)
VAR maxDate = DATE(2023,12,31)

VAR startDate = DATE(YEAR(minDate), 1, 1)
VAR endDate =  DATE(YEAR(maxDate), 12, 31)

RETURN GENERATESERIES(startDate, endDate, 0.04166666667)