Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reynaldo_malave
Helper III
Helper III

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 

reynaldo_malave_1-1623882422360.png


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.

reynaldo_malave_2-1623884111973.png

 

Thanks,


Rey

  

2 REPLIES 2
amitchandak
Super User
Super User

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

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors