Function Dateadd problem

[Antonio_Gomez]

Hello Everyone!

I need some help, please.

I created a matrix with some data from sensors. The columns shows the sensor, the client, the product, the volume that measures, an alert message, the forecast for 7 and 14 days, the days to level 0 and the moment of the last lecture.

Everything was good, but for some reason stopped working.

The forecast formulas was made it by me and are a little tricky.

The ones for 7 and 14 days are similar.

The reasoning is based on the equation of the line and having the last reading and a certain reading in the past, extrapolate the line to future days.

So, I created 8 different forecasts and average all (doing an ensamble technique) to have a better approximation

EnsamblePronostico7Dias =

VAR _Pronostico7DiasVs1DiasAntes = IF([Pronostico7DiasVs1DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs2DiasAntes = IF([Pronostico7DiasVs2DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs3DiasAntes = IF([Pronostico7DiasVs3DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs4DiasAntes = IF([Pronostico7DiasVs4DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs5DiasAntes = IF([Pronostico7DiasVs5DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs6DiasAntes = IF([Pronostico7DiasVs6DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs7DiasAntes = IF([Pronostico7DiasVs7DiasAntes] = 0,0,1)

VAR _Pronostico7DiasVs8DiasAntes = IF([Pronostico7DiasVs8DiasAntes] = 0,0,1)

VAR _Divisor = _Pronostico7DiasVs1DiasAntes

+ _Pronostico7DiasVs2DiasAntes

+ _Pronostico7DiasVs3DiasAntes

+ _Pronostico7DiasVs4DiasAntes

+ _Pronostico7DiasVs5DiasAntes

+ _Pronostico7DiasVs6DiasAntes

+ _Pronostico7DiasVs7DiasAntes

+ _Pronostico7DiasVs8DiasAntes

RETURN

IF(

_Divisor = 0

,[ultimo_volumen]

,( [Pronostico7DiasVs1DiasAntes]

+ [Pronostico7DiasVs2DiasAntes]

+ [Pronostico7DiasVs3DiasAntes]

+ [Pronostico7DiasVs4DiasAntes]

+ [Pronostico7DiasVs5DiasAntes]

+ [Pronostico7DiasVs6DiasAntes]

+ [Pronostico7DiasVs7DiasAntes]

+ [Pronostico7DiasVs8DiasAntes]

) / _Divisor

)

(This 8 times changing only the "DiasAtras")

Pronostico7DiasVs8DiasAntes =

VAR DiasAtras = 8

VAR VolumeHaceDias = CALCULATE([ultimo_volumen], DATEADD(etindustrialcontainerobserved[date],-DiasAtras,DAY))

VAR _Y = [ultimo_volumen] - VolumeHaceDias

VAR _m = _Y / DiasAtras

VAR _b = [ultimo_volumen]

RETURN

IF(

(_m*[7Dias] + _b) > [ultimo_volumen]

,0

,(_m*[7Dias] + _b)

)

VolumenHace8Dias = CALCULATE([ultimo_volumen], DATEADD(etindustrialcontainerobserved[date],-8,DAY))

And for the days to level 0 I did something similar using the equation of the line

EnsambleDiasNivelCero =

VAR _Divisor1Dia = IF([Pendiente1Dia] > -0.5,0,1)

VAR _Divisor2Dias = IF([Pendiente2Dias] > -0.5,0,1)

VAR _Divisor3Dias = IF([Pendiente3Dias] > -0.5,0,1)

VAR _Divisor4Dias = IF([Pendiente4Dias] > -0.5,0,1)

VAR _Divisor5Dias = IF([Pendiente5Dias] > -0.5,0,1)

VAR _Divisor6Dias = IF([Pendiente6Dias] > -0.5,0,1)

VAR _Divisor7Dias = IF([Pendiente7Dias] > -0.5,0,1)

VAR _Divisor8Dias = IF([Pendiente8Dias] > -0.5,0,1)

VAR _Divisor = _Divisor1Dia

+ _Divisor2Dias

+ _Divisor3Dias

+ _Divisor4Dias

+ _Divisor5Dias

+ _Divisor6Dias

+ _Divisor7Dias

+ _Divisor8Dias

VAR _DiasNivelCero1Dia = IF([Pendiente1Dia] > -0.5,0,[DiasNivelCero1Dia])

VAR _DiasNivelCero2Dias = IF([Pendiente2Dias] > -0.5,0,[DiasNivelCero2Dias])

VAR _DiasNivelCero3Dias = IF([Pendiente3Dias] > -0.5,0,[DiasNivelCero3Dias])

VAR _DiasNivelCero4Dias = IF([Pendiente4Dias] > -0.5,0,[DiasNivelCero4Dias])

VAR _DiasNivelCero5Dias = IF([Pendiente5Dias] > -0.5,0,[DiasNivelCero5Dias])

VAR _DiasNivelCero6Dias = IF([Pendiente6Dias] > -0.5,0,[DiasNivelCero6Dias])

VAR _DiasNivelCero7Dias = IF([Pendiente7Dias] > -0.5,0,[DiasNivelCero7Dias])

VAR _DiasNivelCero8Dias = IF([Pendiente8Dias] > -0.5,0,[DiasNivelCero8Dias])

VAR _Numerador = _DiasNivelCero1Dia

+ _DiasNivelCero2Dias

+ _DiasNivelCero3Dias

+ _DiasNivelCero4Dias

+ _DiasNivelCero5Dias

+ _DiasNivelCero6Dias

+ _DiasNivelCero7Dias

+ _DiasNivelCero8Dias

RETURN

IF(

OR(_Numerador / _Divisor > 90, _Divisor = 0)

, "Más de 90 días"

,_Numerador / _Divisor

)

DiasNivelCero8Dias = - [ultimo_volumen] / [Pendiente8Dias]

Pendiente8Dias = IF(

Medidas[Diferencia8Dias] = 0

,-0.1

,Medidas[Diferencia8Dias] / 8

)

Diferencia8Dias = [ultimo_volumen] - [VolumenHace8Dias]

ultimo_volumen = VAR __lastDateTime = MAX(etindustrialcontainerobserved[date_hour_round])

RETURN

CALCULATE([volumen_origen],'Calendario Base'[Dia] = __lastDateTime)

VolumenHace8Dias = CALCULATE([ultimo_volumen], DATEADD(etindustrialcontainerobserved[date],-8,DAY))

I putted all the formulas for giving context but I don't think that's the problem, is something with the data.

The error is for only one sensor , if I filter it the matrix works. The 102 is the problem

[MFelix]

Hi @Antonio_Gomez ,

The code of the error is regarding the timeinteligence formula use in the DATEADD, for this to work you need to use a column that has continuous dates and no gaps.

Believe that in this case you should create a calendar table starting on the 1/Jan/min_year 31/Dec/max_year  related with etindustrialcontainerobserved[date] and the use that column on your dateadd part of the calculation this should solve the message you are getting.

[Antonio_Gomez]

Hi @MFelix 

Thanks for the response. But, I'm using that calendar table you mentioned. The same formulas are applied  to all the sensor but just the 102 is the problem.

This are the details that gives PBI

Feedback Type:
Frown (Error)

Timestamp:
2023-03-13T16:27:59.2294498Z

Local Time:
2023-03-13T17:27:59.2294498+01:00

Session ID:
10f08819-5329-4c19-b8e5-dc22be9a9728

Release:
January 2022

Product Version:
2.100.1381.0 (21.12) (x64)

Error Message:
MdxScript(Model) (515, 64) Calculation error in measure 'Medidas'[VolumenHace8Dias]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

OS Version:
Microsoft Windows NT 10.0.17763.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528049]

Peak Virtual Memory:
70.4 GB

Private Memory:
786 MB

Peak Working Set:
1.01 GB

IE Version:
11.1790.17763.0

User ID:
01414ef9-462c-4ea5-816f-b73c815fd170

Workbook Package Info:
1* - es-ES, Query Groups: 2, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
f:\Users\agomez\AppData\Local\Microsoft\Power BI Desktop SSRS\FrownSnapShot9b4ef1c2-2427-456a-8693-fb933fe28d7c.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
f:\Users\agomez\AppData\Local\Microsoft\Power BI Desktop SSRS\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips
PBI_useModernFormatPane
PBI_sparklines
PBI_scorecardVisual

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI Report Server, Power BI

Formulas:

section Section1;

shared Medidas = let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Columna1 = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Columna1", type text}}),
#"Columnas quitadas" = Table.RemoveColumns(#"Tipo cambiado",{"Columna1"})
in
#"Columnas quitadas";

shared FechaInicioCalendario = #datetime(2021, 12, 16, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

shared DiasaFuturo = 72000 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true];

shared DuracionCalendario = let
Source = DateTime.From(Date.AddDays(DateTime.LocalNow(),DiasaFuturo)),
Duration = Duration.Days(Source - FechaInicioCalendario)
in
Duration;

shared #"Calendario Base" = let
Source = List.DateTimes(FechaInicioCalendario, DuracionCalendario, #duration(0,1,0,0)) // #date(2021,1,1)
,
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Tipo cambiado3" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
Dia = Table.RenameColumns(#"Tipo cambiado3",{{"Column1", "Dia"}}),
#"Added DiaSem" = Table.AddColumn(Dia, "DiaSem", each Date.DayOfWeekName([Dia],"es-ES"), type text),
#"Capitalized Each Word" = Table.TransformColumns(#"Added DiaSem",{{"DiaSem", Text.Proper, type text}}),
#"Added DiaSemNum" = Table.AddColumn(#"Capitalized Each Word", "DiaSemNum", each Date.DayOfWeek([Dia],Day.Monday)+1, type text),
// DiaSemNum a número
#"Tipo cambiado" = Table.TransformColumnTypes(#"Added DiaSemNum",{{"DiaSemNum", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Tipo cambiado", "Año", each Date.Year([Dia]), Int64.Type),
#"Inserted MonthNumber" = Table.AddColumn(#"Inserted Year", "NumMes", each Date.Month([Dia]), type text),
// Comentarios detallados del paso
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Inserted MonthNumber",{{"NumMes", Int64.Type}}),
#"Inserted MonthName" = Table.AddColumn(#"Tipo cambiado1", "Mes", each Date.MonthName([Dia], "es-ES"), type text),
#"Capitalized Month Name" = Table.TransformColumns(#"Inserted MonthName",{{"Mes", Text.Proper, type text}}),
#"Inserted Quarter" = Table.AddColumn(#"Capitalized Month Name", "Trim", each "T" & Number.ToText(Date.QuarterOfYear([Dia]))),
#"Added Semester" = Table.AddColumn(#"Inserted Quarter", "Semes", each "S" & Number.ToText(Number.RoundUp ([NumMes]/6))),
#"Added Semana" = Table.AddColumn(#"Added Semester", "Semana", each "Sema_" & Number.ToText(Date.WeekOfYear([Dia]))),
#"Agregar Quincena" = Table.AddColumn(#"Added Semana", "Personalizado", each if (Date.Day([Dia])) <= 15 then 1 else 2),
#"Renombrar Personalizado a Quin_num" = Table.RenameColumns(#"Agregar Quincena",{{"Personalizado", "Quin_num"}}),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Renombrar Personalizado a Quin_num",{{"Quin_num", Int64.Type}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado2", "Personalizado", each if Date.Day([Dia]) < 15 then "Q1" else "Q2"),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Personalizada agregada",{{"Personalizado", "Quin_Desc"}}),
#"Added Año_Mes" = Table.AddColumn(#"Columnas con nombre cambiado1", "Año-Mes", each [Mes] & "-" & Number.ToText([Año])),
#"Inserted Day of Year" = Table.AddColumn(#"Added Año_Mes", "Juliano2", each Date.DayOfYear([Dia]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Day of Year",{"Juliano2"}),
#"Inserted Day of Year1" = Table.AddColumn(#"Removed Columns", "DiaJuliano", each Date.DayOfYear([Dia]), Int64.Type),
#"Tipo cambiado4" = Table.TransformColumnTypes(#"Inserted Day of Year1",{{"Trim", type text}, {"Semes", type text}, {"Semana", type text}, {"Quin_Desc", type text}, {"Año-Mes", type text}}),
#"Hora insertada" = Table.AddColumn(#"Tipo cambiado4", "Hora", each Time.From([Dia]), type time),
#"Día insertado" = Table.AddColumn(#"Hora insertada", "DiaNum", each Date.Day([Dia]), Int64.Type),
#"Filas filtradas" = Table.SelectRows(#"Día insertado", each true)
in
#"Filas filtradas";

shared etindustrialcontainerobserved = let
Origen = Odbc.DataSource("dsn=AscanioPostgreSQL", [HierarchicalNavigation=true]),
mtchemical_Schema = Origen{[Name="mtchemical",Kind="Schema"]}[Data],
etindustrialcontainerobserved_Table = mtchemical_Schema{[Name="etindustrialcontainerobserved",Kind="Table"]}[Data],
#"Texto insertado después del delimitador" = Table.AddColumn(etindustrialcontainerobserved_Table, "dep_id", each Text.AfterDelimiter([entity_id], ":", 3), type text),
#"Fecha insertada" = Table.AddColumn(#"Texto insertado después del delimitador", "date", each Date.From([timestamp]), type date),
#"Hora insertada" = Table.AddColumn(#"Fecha insertada", "hour", each Time.From([timestamp]), type time),
#"Inicio de la hora calculado" = Table.TransformColumns(#"Hora insertada",{{"hour", Time.StartOfHour, type time}}),
#"Texto insertado entre delimitadores" = Table.AddColumn(#"Inicio de la hora calculado", "lat", each Text.BetweenDelimiters([location_centroid], "(", ","), type text),
#"Valor reemplazado" = Table.ReplaceValue(#"Texto insertado entre delimitadores",".",",",Replacer.ReplaceText,{"lat"}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Valor reemplazado",{{"lat", type number}}),
#"Texto insertado entre delimitadores1" = Table.AddColumn(#"Tipo cambiado", "long", each Text.BetweenDelimiters([location_centroid], ",", ")"), type text),
#"Valor reemplazado1" = Table.ReplaceValue(#"Texto insertado entre delimitadores1",".",",",Replacer.ReplaceText,{"long"}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Valor reemplazado1",{{"long", type number}}),
#"Columna condicional agregada" = Table.AddColumn(#"Tipo cambiado1", "volume_corregido", each if [volume] > 1000 then 1000 else [volume]),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Columna condicional agregada",{{"volume_corregido", type number}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado2", "level_max", each 1000),
#"Tipo cambiado3" = Table.TransformColumnTypes(#"Personalizada agregada",{{"level_max", Int64.Type}}),
#"Personalizada agregada1" = Table.AddColumn(#"Tipo cambiado3", "level_msg", each "Nivel Bajo"),
#"Tipo cambiado4" = Table.TransformColumnTypes(#"Personalizada agregada1",{{"level_msg", type text}}),
#"Columna condicional agregada1" = Table.AddColumn(#"Tipo cambiado4", "unidades", each if Text.Contains([unit], "l") then "Litros" else null),
#"Tipo cambiado5" = Table.TransformColumnTypes(#"Columna condicional agregada1",{{"unidades", type text}}),
#"Inicio de la hora insertado" = Table.AddColumn(#"Tipo cambiado5", "date_hour_round", each Time.StartOfHour([timestamp]), type datetime),
#"Final de la hora calculado" = Table.TransformColumns(#"Inicio de la hora insertado",{{"date_hour_round", Time.EndOfHour, type datetime}}),
#"Personalizada agregada2" = Table.AddColumn(#"Final de la hora calculado", "level_min", each 0),
#"Tipo cambiado6" = Table.TransformColumnTypes(#"Personalizada agregada2",{{"level_min", Int64.Type}}),
#"Texto insertado después del delimitador1" = Table.AddColumn(#"Tipo cambiado6", "Sensor", each Text.AfterDelimiter([dep_id], "7"), type text),
#"Sorted Rows" = Table.Sort(#"Texto insertado después del delimitador1",{{"timestamp", Order.Descending}})
in
#"Sorted Rows";

shared #"Ubicacion-Producto(TablaInterna)" = let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdbdbqJAFADgVyFe23T+Z+AO8A81pinW/qUXZmUbkxYb0Jt9rX2EfbFFqOMMF6SjICZGOcDFl3POnOPrawfAW4BuEUCo0+3sktiJ39P1zcfKWfSH7l2IsOOGvkMpphwCmL3Ti77+/U3X2411iNPtOt5YCGfBXbJZ7X5lz+7vsyhcxtul5Se7P9YqsuZRHCXr31FXXmWvlD9v3YyEDEiiRBIqiaqkMLwCkiaaz88QsZrqBrlKeng4gyR+TAK2rZEOcUHSOunx8SIiCFgpSewogkwVLRani6BJI4GSCKi9LVTS09MZJJNOIiUSUUlAJT0/X4hUUTcLaq308nIhEi+RuELS6ua6rjkJiS5hzEZ5V95A1kUcZjdg4eR1DU8N6nle3VCTs1k5wLSZ6vv+GUW2azoKUEter9c73YRwTVM1D6Sp3+/XXFADaPVoKyIpHQwGbUorWy+PpHQ4HLYpRSUp0qRIlY5GozaluCTFmlSb2UEQtCmtPOR5JKXj8bhNKS1JqSbVZuRkMmlTWrmwgfZXazqdtind7+3A8783eP/zK4nSpRX0HAvkGXVX3scu+plwP9MQ3X9n823/M11vk0367bURIJILBRbUXFu1zYtI5nU2m52+kUC2JbHJRtqnsLhqNIWQC3xMISO2qbZYPEXBQcNaxgGnBy2z6QlYKLGw+dTa6JhazoS5FkktalxLEDlqCSXmWiy1+PobgUgsaTy1NlHPGOPmWiq19PpTyySWXT9WSKy4PPbtPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fecha = _t, entity_id = _t, #"Deposito Nombre" = _t, #"Empresa Destino" = _t, Producto = _t, Localidad = _t, #"Nivel Vacío" = _t, #"Nivel Alarma" = _t, #"Nivel Máximo" = _t, Medida = _t, Lat = _t, Long = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"entity_id", type text}, {"Deposito Nombre", type text}, {"Empresa Destino", type text}, {"Producto", type text}, {"Localidad", type text}, {"Nivel Vacío", type number}, {"Nivel Alarma", type number}, {"Nivel Máximo", type number}, {"Medida", type text}}),
#"Filas ordenadas" = Table.Sort(#"Tipo cambiado",{{"Fecha", Order.Descending}}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Filas ordenadas",{{"Lat", type number}, {"Long", type number}, {"Fecha", type datetime}})
in
#"Tipo cambiado1";

shared DatosComplementarios = let
Source = Excel.Workbook(Web.Contents(), null, true),
Data0 = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fecha", type date}, {"entity_id", type text}, {"Empresa Destino", type text}, {"Producto", type text}, {"Nivel Vacío", Int64.Type}, {"Nivel Alarma", Int64.Type}, {"Nivel Máximo", Int64.Type}, {"Medida", type text}, {"Sensor", Int64.Type}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Blank Rows", "Coordenadas", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LatitudManual", "LongitudManual"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",".",",",Replacer.ReplaceText,{"LatitudManual", "LongitudManual"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "MostrarCoord", each if Text.Contains([#"¿Mostrar Estas Coord?"], "s") then 1 else if Text.Contains([#"¿Mostrar Estas Coord?"], "S") then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"LatitudManual", type number}, {"LongitudManual", type number}, {"¿Mostrar Estas Coord?", type text}, {"MostrarCoord", Int64.Type}})
in
#"Changed Type1";

[MFelix]

Hi @Antonio_Gomez ,

You are using the calendar table however in your calculation you are refering to the etindustrialcontainerobserved table, or did you change this already?

VolumenHace8Dias = CALCULATE([ultimo_volumen], DATEADD(etindustrialcontainerobserved[date],-8,DAY))

The formula should be:

VolumenHace8Dias = CALCULATE([ultimo_volumen], DATEADD('Calendario Base'[date],-8,DAY))

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Antonio_Gomez]

Hi @MFelix 

I've changed etindustrialcontainerobserved[date] for 'Calendario Base'[date] in all the formulas and now all the sensors give error, not only the 102.

I don't know how to share the mockup, can you explain me step by step, please?

[MFelix · Accepted solution]

Hi @Antonio_Gomez ,

Does your calendar table is a date/time format?

To share a file you can use a onedrive, google drive, we transfer or similar link to upload your files. If the information is sensitive please share it trough private message.

[Antonio_Gomez]

Hi @MFelix 

Finally with your comments my problem was solve.

Although I was using a Calendar table, the format of the columns were different. One was date+hour and the other was date.

And, I changed the formulas to:

VolumenHace8Dias =

VAR __lastDate = MAX(etindustrialcontainerobserved[date])

VAR DiasAtras = 8

RETURN

CALCULATE(

[ultimo_volumen],

FILTER(

etindustrialcontainerobserved,

etindustrialcontainerobserved[date] = __lastDate - DiasAtras)

)

To explain why the PBI was working well and suddenly not, it is about showing the actual volume of products in tanks and forecasting their volume.

When one sensor stopped sending data for a whole day, missing data such as the date appeared. The contiguous error happened because I was using the dates from the sensor's table and not the ones from the Calendar table.

After changing the date column, formulas and direction in the relationships between tables the problem was solve!!!

[Antonio_Gomez]

Hi @MFelix 

Yes, both columns have the date/time format. The one of the Calendar (named dia) and the etindustrialcontainerobserved (named date_hour_round).

I'll share via Google Drive a copy of the file, because the PBI is directly connected to a Data Base.

I'll send you a private message with the link

Thank you very much