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.

     

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.