Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |