Si es el primer día de la nueva semana, obtén los datos de la semana pasada.

Un usuario Pregunto ✅

sgsukumaran

Tengo una consulta que calcula WTD solo para la semana actual y se usa en una tabla calculada.

WTD actual del presupuesto de $: =
CALCULAR(
[Budget $’s],
FILTRAR(
VALUES (‘Fecha del evento'[Event Current Week Offset]),
‘Fecha del evento'[Event Current Week Offset] = 0
),
FILTRAR(
VALUES (‘Fecha del evento'[Event Current Day Offset]),
‘Fecha del evento'[Event Current Day Offset] <= -1
)
)

Esto funciona bien hasta el primer día de la semana. Al comienzo de la semana (domingo) no hay datos en ese momento, por lo que vuelve en blanco. En su lugar, a la empresa le gustaría mostrar los datos de la semana anterior solo al comienzo de la semana. ¿Algún consejo?

Dimensión de días originales

select -- The following are the required columns from Dimensions.Days
	   d.DateKey, 
	   d.ActualDate, 
	   d.YearNumber, 
	   d.QuarterNumber, 
	   d.MonthNumber, 
	   d.MonthOfYear, 
	   d.MonthShortDescription,
	   d.DayNumberOfMonth, 

	   --
	   -- The following columns represents the day number within a year
	   --
	   case 
		when
			d.ActualDate 
				between (select min(ActualDate) from Dimensions.Days where YearNumber=year(d.ActualDate))
				and 
						(select max(ActualDate) from Dimensions.Days where YearNumber=year(d.ActualDate))
				then datediff(day,(select min(ActualDate) from Dimensions.Days where YearNumber=year(d.ActualDate)),d.ActualDate)+1
		else null
	   end as DayNumberOfYear,

	   d.DayOfWeekNumber, 
	   d.DayOfWeekDesc, 
	   d.LastDayInMonth, 
	   d.LastDayInWeek, 
	   d.HolidayInd, 
	   d.WeekDayInd,
	   d.WeekStartingDate, 
	   d.WeekEndingDate, 
	   d.YearMonth, 
	   d.YearHalf,
	   d.YearQuarter, 
	   d.Season,

	   --
	   -- The following column provides the date key for the same DOW in the previous year.
	   (case
			when d.YearNumber=year(getdate()) then (select DateKey from Dimensions.Days where ActualDate=dateadd(week,-52,d.ActualDate))
			else (select 100000+COUNT(*) from Dimensions.Days t2 where d.DateKey >= t2.DateKey)
	   end) as LYSameDayOfWeekDateKey,

	   --
	   -- The following columns are used to filter measures and visuals for time intelligence purposes
	   --
       datediff(day, convert(date,getdate()),d.ActualDate) as CurrentDayOffset,

       ((YearNumber - year(getdate())) * 12) + (MonthNumber - month(getdate())) as CurrentMonthOffset,
	   
       ((YearNumber - year(getdate())) * 4) + (QuarterNumber - datepart(Q, getdate())) as CurrentQuarterOffset,

	   (YearNumber - year(getdate())) as CurrentYearOffset,

	   ((YearNumber - year(getdate())) * 52) + (Datepart(Week,ActualDate) - Datepart(Week,getdate())) as CurrentWeekOffset,

	   case 
			when ActualDate > convert(date,getdate()) then 'Future'
			when ActualDate = convert(date,getdate()) then 'Today'
			else 'Past'
	   end as DateTense,

	   --case	when YearNumber = year(getdate()) and MonthNumber = month(getdate()) and ActualDate <= getdate() then 1 else 0 end as WTDFilter,
	   case	when YearNumber = year(getdate()) and MonthNumber = month(getdate()) and ActualDate <= getdate() then 1 else 0 end as MTDFilter,
	   --case	when YearNumber = year(getdate()) and QuarterNumber = datepart(Q, getdate()) and ActualDate <= getdate() then 1 else 0 end as QTDFilter,
	   case	when YearNumber = year(getdate()) and MonthNumber <= month(getdate()) and ActualDate <= getdate() then 1 else 0 end as YTDFilter,

	   case 
			when YearNumber = year(getdate()) and MonthNumber = month(getdate()) and day(getdate()) > 1 and ActualDate = convert(date,getdate()-1) then 'Current Month (Thru ' + SUBSTRING(DATENAME(MM, GETDATE()),1,3) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) +')'
			when YearNumber = year(getdate()) and MonthNumber = month(getdate()) then 'Current Month' 
			--when YearNumber = year(getdate()) and MonthNumber = month(getdate()) then 'Current Month' 
			else YearMonth 
	   end as DatePeriod,

	   case 
			when YearNumber = year(getdate()-1) and MonthNumber = month(getdate()-1) then 'Y' 
			else 'N' 
	   end as TargetRevenueMonth,

	   case 
			when YearNumber = year(getdate()-1) then 'Y' 
			else 'N' 
	   end as TargetRevenueYear

from Dimensions.Days d

where d.YearNumber >= year(getdate())-4 
and   d.YearNumber <= year(getdate())+1

Hola @sgsukumaran,

¿Podría compartir sus datos de muestra y la excepción a mí?

Saludos,

Franco

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *