cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harsha087 Helper III
Helper III

Prior date of the week

Hi All,

*** i m working on direct query mode*****

 

i have as_of_date and day_of_week in my db . i want to create a dax for prior date . Prior date is calculated for the previous day . So, for weekend we need to consider the last week friday date . 

 

Below is the example 

as_of_dateDAY_OF_WEEKPrior date
5/15/2011Sunday5/13/2011
5/16/2011Monday5/13/2011
5/17/2011Tuesday5/16/2011
5/18/2011Wednesday5/17/2011
5/19/2011Thursday5/18/2011
5/20/2011Friday5/19/2011
5/21/2011Saturday5/20/2011
5/22/2011Sunday5/20/2011
5/23/2011Monday5/20/2011
5/24/2011Tuesday5/23/2011

 

Kindly help me ..

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Prior date of the week

Hello @harsha087 

Add this as a column in your table, should get you what you want.

Prior Date = 
VAR OffSet = 
SWITCH ( TRUE(),
    WEEKDAY('Table'[as_of_date]) = 2, 3,
    WEEKDAY('Table'[as_of_date]) = 1, 2,
    1)
RETURN 'Table'[as_of_date] - OffSet

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Prior date of the week

Hello @harsha087 

Add this as a column in your table, should get you what you want.

Prior Date = 
VAR OffSet = 
SWITCH ( TRUE(),
    WEEKDAY('Table'[as_of_date]) = 2, 3,
    WEEKDAY('Table'[as_of_date]) = 1, 2,
    1)
RETURN 'Table'[as_of_date] - OffSet

View solution in original post

harsha087 Helper III
Helper III

Re: Prior date of the week

thanks a lot ... its working ...

harsha087 Helper III
Helper III

Re: Prior date of the week

Small change in the requirement :

 

using the BUSINESS_DAY_IN_MNTH, we need to calculate the Prior date as tomorrow as_of_date and DAY_OF_WEEK will be removed . Could you kindly help me plz..

DAY_OF_WEEKas_of_dateBUSINESS_DAY_IN_MNTHDAY_NUM_IN_MNTHPrior date
Sunday5/15/201110155/13/2011
Monday5/16/201111165/13/2011
Tuesday5/17/201112175/16/2011
Wednesday5/18/201113185/17/2011
Thursday5/19/201114195/18/2011
Friday5/20/201115205/19/2011
Saturday5/21/201115215/20/2011
Sunday5/22/201115225/20/2011
Monday5/23/201116235/20/2011
Tuesday5/24/201117245/23/2011

 

Super User IV
Super User IV

Re: Prior date of the week

You are going to need something in the table to tell you what month the BUSINESS_DAY_IN_MNTH field belongs to.

harsha087 Helper III
Helper III

Re: Prior date of the week

Sir,

 

in a year , the BUSINESS_DAY_IN_MNTH are having values from 0 to 365(or any value) . If the day is weekend(sat or sun) or 25th Dec  & 1st Jan (hoildays) then we see same number assigned in that BUSINESS_DAY_IN_MNTH. Then we need to populate the prior business working days in to prior date column . 

 

 

 

Thanks 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

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

Top Solution Authors
Top Kudoed Authors