Prior Date logic needed

[Anonymous]

i have these fields (as_of_date,BUSINESS_DAY_IN_MNTH,DAY_NUM_IN_MNTH) in my db. now i want to calculate the prior business day(Prior date) . Consider weekends(sat and sun) , 1st jan and 25th jan are hoildays . 

for example:

if that date in the as_of_date fall on hoilday we need to  get last working day or if the date in the as_of_date fall on weekday we need get to previous working day. Below is the table for refernece .

as_of_date	BUSINESS_DAY_IN_MNTH	DAY_NUM_IN_MNTH	Prior date
5/15/2011	10	15	5/13/2011
5/16/2011	11	16	5/13/2011
5/17/2011	12	17	5/16/2011
5/18/2011	13	18	5/17/2011
5/19/2011	14	19	5/18/2011
5/20/2011	15	20	5/19/2011
5/21/2011	15	21	5/20/2011
5/22/2011	15	22	5/20/2011
5/23/2011	16	23	5/20/2011
5/24/2011	17	24	5/23/2011

[amitchandak]

Do you have some column that contains holidays

[Anonymous]

ya sir ..

like the below table . On 1st jan is holiday we get 12/31 . for 2 nd jan , we get 12/31 as 1st jan was hoilday . 

AS_OF_DATE	BUSINESS_DAY_IN_MNTH	DAY_NUM_IN_MNTH	Prior Date
1/1/2009	0	1	12/31/2008
1/2/2009	1	2	12/31/2008
1/3/2009	1	3	1/2/2009
1/4/2009	1	4	1/2/2009
1/5/2009	2	5	1/2/2009

[v-juanli-msft]

Hi @Anonymous 

Create a new table

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "weeknum", WEEKNUM ( [Date], 2 ),
    "weekday", WEEKDAY ( [Date], 2 ),
    "is or not workday", IF (
        WEEKDAY ( [Date], 2 ) IN { 6, 7 }
            || (
                MONTH ( [Date] ) = 1
                    && DAY ( [Date] ) = 1
            )
            || (
                MONTH ( [Date] ) = 1
                    && DAY ( [Date] ) = 25
            ),
        "not workday",
        "workday"
    )
)

Create a calculated column

Column = CALCULATE(MAX('date'[Date]),FILTER(ALL('date'),'date'[Date]<EARLIER('Table'[as_of_date])&&[is or not workday]="workday"))

Best Regards
Maggie

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

[Anonymous]

Thanks for your response .. 

one doubt , is the earlier function works in the direct query .. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As tested, earlier function doesn't work for direct query, create a measure instead

Measure = CALCULATE(MAX('date'[Date]),FILTER(ALL('date'),'date'[Date]<MAX('Sheet1$'[as_of_date])&&[is or not workday]="workday"))

Best Regards
Maggie

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