cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pratk30 Regular Visitor
Regular Visitor

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
Moderator v-yuezhe-msft
Moderator

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 58 members 1,197 guests
Please welcome our newest community members: