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 ...
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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Do you have some column that contains holidays

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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your response .. 

       

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

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        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.