Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Folks,
I have some difficulties in calculating dates which are using in my report for filtering.
I have one date column in a table and need to filter this table based on current week start date. for e.g.
date column < calculated_current_week_start_date
This is one requirement.
Is there any way to store this kind of data like in seperate measure table ?
I need to create some variable like below SQL statements :
DECLARE @UserDate date;
Declare @WeekStart date;
Declare @WeekEnd date;
Declare @MonthStart date;
Declare @MonthEnd date;
Declare @YearStart date;
Declare @YearEnd date;
Set @UserDate = GETDATE();
Set @WeekStart = DATEADD("D",-1*(DatePart("DW",@UserDate)-1),@UserDate);
Set @WeekEnd = DATEADD("D",6,@WeekStart);
Set @MonthStart = DATEADD("D",-1*(DatePart("D",@UserDate)-1),@UserDate);
Set @MonthEnd = DATEADD("D",-1,DateAdd("M",1,@MonthStart));
Set @YearStart = DATEADD("D",-1*(DatePart("DY",@UserDate)-1),@UserDate);
Set @YearEnd = DATEADD("D",-1,DateAdd("YY",1,@YearStart));
Any possible solution please suggest.
Thanks in advance!
@pratk30,
Regarding to your first requirement, do you mean that date column<1/15/2018? If so, create a column in your table using DAX below.
checkColumn = IF(Table[Date]<(Today()-Weekday(Today())+2),1,0)
Regarding to your second requirement, could you please share sample data of your table and post expected result?
Regards,
Lydia
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |