Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @sgsukumaran,
Could you please share your sample data and excepted reuslt to me ?
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |