Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
(This 8 times changing only the "DiasAtras")
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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";
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
89 | |
76 | |
66 | |
60 |