Forum Discussion

reynaldo_malave's avatar
reynaldo_malave
Helper III
5 years ago

adding forecasted future dates

Hi Guys,

 

I have a forecast measure (variable inside a measure). I want to use this measure inside a table and summarize from today until the eomonth. This is what it looks like. 

Troubleshooting part

 

var Forecast = --This is the result of priorly calculated variables
       
    ( FactorCrecimientoMM * VentaMismoDiaSemanaMesAnoAnterior ) + VentaMismoDiaSemanaMesAnoAnterior 
    
var today =

    TODAY()

var FutureDaysTable =

    ADDCOLUMNS(                          
        SUMMARIZE(                      --Group calendar table by month
            Calendar,
            Calendar [month]
        ),
        "Forecast", Forecast            --Add variable Forecast as a column to the table
        ) 

        
var RollingFutureDays =

    CALCULATE(
        SUM( [Importe Proyección] ),        --sum recently created column in FutureDaysTable
        DM_calendario[Fecha] >= hoy,        --from today
        DM_calendario[Mes Número] = Mes     --until the end of the month
    )

return

    RollingFutureDays

 

 

When I use Forecast after return it works fine on daily basis but i notice there is no total 


To be more clear of what I want to achive it looks like this. The number I am looking for is $ 2.096. Is the result  of substracting the sum of forecast (from may 1st to the 26th ) from the total forecasted for may 13.

 

Thanks,


Rey

  

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    reynaldo_malave , based on what I got. Try like

     


    measure =
    var Forecast = --This is the result of priorly calculated variables

    ( FactorCrecimientoMM * VentaMismoDiaSemanaMesAnoAnterior ) + VentaMismoDiaSemanaMesAnoAnterior
    return
    CALCULATE(
    SUM( Forecast), --sum recently created column in FutureDaysTable
    DM_calendario[Fecha] >= max(DM_calendario[Fecha]), --from today
    DM_calendario[Mes Número] = max(DM_calendario[Mes Número] ) --until the end of the month
    )

    • reynaldo_malave's avatar
      reynaldo_malave
      Helper III
      5 years ago

      HI amitchandak,

       

      Thanks for your reply. You are always looking for post to help people so thanks for that too.

       

      It looks like its the way to go, but it would not let me use sum on forecast. It was my first attempt.

       

      Here is the entire code is not gonna be translated but you get the idea 

       

      Importe Proyección = 

var FechaSeleccionada =

    SELECTEDVALUE( DM_calendario[Fecha] )                 -- Selected Date from calendar

var FechaSeleccionadaAnoAnterior =

    FechaSeleccionada - 365                               -- Selected date one year back

var DiaSemana =

    WEEKDAY( FechaSeleccionada, 3 )                       -- weekday selected date

var Mes =

    MONTH( FechaSeleccionada )                            -- month selected date

var Ano =

    YEAR( FechaSeleccionada )

var AnoAnterior =

    YEAR( FechaSeleccionadaAnoAnterior )

var VentaDiaSemanaAnoActual =

    CALCULATE(
        sum ( Proyeccion[total_venta] ),
        DM_calendario[Numero Día Semana] = DiaSemana,
        DM_calendario[Año] = Ano,
        DM_calendario[Fecha] < FechaSeleccionada
    )

var VentaDiaSemanaAnoAnterior =

    CALCULATE(
        sum ( Proyeccion[total_venta] ),
        DM_calendario[Numero Día Semana] = DiaSemana,
        DM_calendario[Año] = AnoAnterior,
        DM_calendario[Fecha] < FechaSeleccionadaAnoAnterior
    )

var FactorCrecimientoMM =

    DIVIDE(
        VentaDiaSemanaAnoActual,
        VentaDiaSemanaAnoAnterior
    ) - 1

var InstanceNumberActual =

    CALCULATE(
        MIN( DM_calendario[instance number] ),
        DM_calendario[Fecha] = FechaSeleccionada
    )

var VentaMismoDiaSemanaMesAnoAnterior =

    CALCULATE(
        SUM( Proyeccion[total_venta] ), 
        DM_calendario[Fecha] < FechaSeleccionada,       
        DM_calendario[Numero Día Semana] = DiaSemana,
        DM_calendario[Año] = AnoAnterior,
        DM_calendario[Mes Número] = Mes,
        DM_calendario[instance number] = InstanceNumberActual
    )

var ImporteProyeccion =
       
    ( FactorCrecimientoMM * VentaMismoDiaSemanaMesAnoAnterior ) + VentaMismoDiaSemanaMesAnoAnterior 
    
var hoy =

    TODAY()

var ImporteProyeccionMensual =

    ADDCOLUMNS(
        SUMMARIZE(
            DM_calendario,
            DM_calendario[Mes Número]
        ),
        "Importe Proyección", ImporteProyeccion
    )


/*        
var MTDDiasFuturos =

    CALCULATE(
        SUM( [Importe Proyección] ),
        DM_calendario[Fecha] >= hoy,
        DM_calendario[Mes Número] = Mes
    )
*/ 
return

    ImporteProyeccion