cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

Hello Everyone!

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 _Y = [ultimo_volumen] - VolumeHaceDias

VAR _m = _Y / DiasAtras

VAR _b = [ultimo_volumen]

RETURN
IF(
(_m*[7Dias] + _b) > [ultimo_volumen]
,0
,(_m*[7Dias] + _b)
)

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])
+ _DiasNivelCero2Dias
+ _DiasNivelCero3Dias
+ _DiasNivelCero4Dias
+ _DiasNivelCero5Dias
+ _DiasNivelCero6Dias
+ _DiasNivelCero7Dias
+ _DiasNivelCero8Dias

RETURN
IF(
OR(_Numerador / _Divisor > 90, _Divisor = 0)
, "Más de 90 días"
)

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)

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

1 ACCEPTED SOLUTION
Super User

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ês

7 REPLIES 7
Super User

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ês

Resolver I

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}}),
in

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
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}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Added DiaSem",{{"DiaSem", Text.Proper, type text}}),
// DiaSemNum a número
#"Inserted MonthNumber" = Table.AddColumn(#"Inserted Year", "NumMes", each Date.Month([Dia]), type text),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Inserted MonthNumber",{{"NumMes", Int64.Type}}),
#"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]))),
#"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}}),
in

shared etindustrialcontainerobserved = let
mtchemical_Schema = Origen{[Name="mtchemical",Kind="Schema"]}[Data],
etindustrialcontainerobserved_Table = mtchemical_Schema{[Name="etindustrialcontainerobserved",Kind="Table"]}[Data],
#"Final de la hora calculado" = Table.TransformColumns(#"Inicio de la hora insertado",{{"date_hour_round", Time.EndOfHour, type datetime}}),
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}}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Filas ordenadas",{{"Lat", type number}, {"Long", type number}, {"Fecha", type datetime}})
in

shared DatosComplementarios = let
Source = Excel.Workbook(Web.Contents(), null, true),
Data0 = Source{0}[Data],
#"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";

Super User

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))``

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ês

Resolver I

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?

Super User

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ês

Resolver I

Hi @MFelix

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!!!

Resolver I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors