Function Dateadd problem

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";