cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Date Calculation like SQL

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!

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Date Calculation like SQL

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors