Date Calculation like SQL

[pratk30]

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!

[v-yuezhe-msft]

@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