Forum Discussion

Antonio_Gomez's avatar
Antonio_Gomez
Resolver I
3 years ago
Solved

Function Dateadd problem

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
 

 

Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    3 years ago

    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.

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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's avatar
      Antonio_Gomez
      Resolver I
      3 years ago

      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's avatar
        MFelix
        Super User
        3 years ago

        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.