I have a Query that calculates WTD just for Current week and it is used in a Calculated table.
Budget $'s Current WTD:=
CALCULATE(
[Budget $'s],
FILTER(
VALUES('Event Date'[Event Current Week Offset]),
'Event Date'[Event Current Week Offset] =0
),
FILTER(
VALUES('Event Date'[Event Current Day Offset]),
'Event Date'[Event Current Day Offset] <= -1
)
)
This Works fine until the first day of the week. At the start of the week(Sunday) there is no data at that point so it returns blank. Instead the business would like to show Previous week data only on the start of the week. Any pointers?
Original Days Dimension
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
