Prior date of the week

[Anonymous]

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_date	DAY_OF_WEEK	Prior date
5/15/2011	Sunday	5/13/2011
5/16/2011	Monday	5/13/2011
5/17/2011	Tuesday	5/16/2011
5/18/2011	Wednesday	5/17/2011
5/19/2011	Thursday	5/18/2011
5/20/2011	Friday	5/19/2011
5/21/2011	Saturday	5/20/2011
5/22/2011	Sunday	5/20/2011
5/23/2011	Monday	5/20/2011
5/24/2011	Tuesday	5/23/2011

Kindly help me ..

Thanks

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

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

[Anonymous]

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_WEEK	as_of_date	BUSINESS_DAY_IN_MNTH	DAY_NUM_IN_MNTH	Prior date
Sunday	5/15/2011	10	15	5/13/2011
Monday	5/16/2011	11	16	5/13/2011
Tuesday	5/17/2011	12	17	5/16/2011
Wednesday	5/18/2011	13	18	5/17/2011
Thursday	5/19/2011	14	19	5/18/2011
Friday	5/20/2011	15	20	5/19/2011
Saturday	5/21/2011	15	21	5/20/2011
Sunday	5/22/2011	15	22	5/20/2011
Monday	5/23/2011	16	23	5/20/2011
Tuesday	5/24/2011	17	24	5/23/2011

[jdbuchanan71]

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

[Anonymous]

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 

[Anonymous]

thanks a lot ... its working ...