Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Prior Date logic needed

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_dateBUSINESS_DAY_IN_MNTHDAY_NUM_IN_MNTHPrior date
5/15/201110155/13/2011
5/16/201111165/13/2011
5/17/201112175/16/2011
5/18/201113185/17/2011
5/19/201114195/18/2011
5/20/201115205/19/2011
5/21/201115215/20/2011
5/22/201115225/20/2011
5/23/201116235/20/2011
5/24/201117245/23/2011

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

     

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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_DATEBUSINESS_DAY_IN_MNTHDAY_NUM_IN_MNTHPrior Date
      1/1/20090112/31/2008
      1/2/20091212/31/2008
      1/3/2009131/2/2009
      1/4/2009141/2/2009
      1/5/2009251/2/2009
      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        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.