Power BI Calculate measure for previous working day

[minishshah]

Hello,

I need help creating a measure that will count the total merch booked from a previous working day. 

I currently have the following:

1. dimDate table

A. This table contains following:

  i. Date Column

 ii. Dayofweek column: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday

iii. Working Day column: indicates whether it is a "Working" or "Non-Working" day based on the combination of "Dayofweek" and "Holiday" Column. 

2.  Total Merch Booked measure

Here are the conditions that this previous day measure should follow:

1. Weekday (TODAY()) = 2 (Monday), then it should look whether the Friday before was a working day, if so, then it should calculate Total Merch booked on that day, otherwise keep repeating to the previous day to it until it finds a working day and then calculate Total Merch Booked.

2. Weekday(TODAY()) =1 OR Weekday (TODAY()) =7 (Saturday or Sunday), then skip and do not calculate the Total Merch booked.

3. Weekday(TODAY()) = any other number besides 1, 2 or 7 (Tuesday thru Friday), then it should look at the previous day to see if it is a Working day, if so, then it should calculate Total Merch booked on that day, otherwise going in backwards until it finds a working day and then calculate Total Merch booked. 

[amitchandak]

@minishshah , refer to my blog -.Traveling Across Workdays -
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[minishshah]

@amitchandak ,

Thank you for posting a link. it is interesting, but not quiet what I am looking for as stated in my initial post. 

Further, I think i need to use combination of two or more columns which i indicated on my post to figure it out, but not sure. 

I tried to use the following, but i also need to count 'holidays' in to the mix:

IF(

    WEEKDAY(TODAY()) = 2,

   CALCULATE(

       [Total Merch Booked],

       'dimDate'[Date]= (TODAY()-3)

   ),

    IF(

        WEEKDAY(TODAY()) = 1,

        BLANK(),

        IF(

            WEEKDAY(TODAY()) = 7,

            BLANK(),

            CALCULATE(

                [Total Merch Booked],

                'dimDate' [Date] = (TODAY()-1)

            )

        )

    )

[minishshah]

I'm still needing assistance. Please help

[DataInsights · Accepted solution]

@minishshah, try this (adjust for your table/column names):

1. Create a calculated column in your date table:

Working Day Number Cumulative = 
VAR vWorkdays =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Is Working Day] = 1
    )
VAR vResult =
    IF (
        'Date'[Is Working Day] = 0,
        BLANK (),
        RANKX ( vWorkdays, 'Date'[Date],, ASC )
    )
RETURN
    vResult

2. Create a measure:

Previous Day Sales =
VAR vWorkingDaysOffset = 1
VAR vMaxWorkingDay =
    MAX ( 'Date'[Working Day Number Cumulative] )
VAR vPreviousWorkingDay =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Working Day Number Cumulative] = vMaxWorkingDay - vWorkingDaysOffset
    )
VAR vPreviousDate =
    MAXX ( vPreviousWorkingDay, 'Date'[Date] )
VAR vResult =
    CALCULATE ( [Total Amount], ALL ( 'Date' ), 'Date'[Date] = vPreviousDate )
RETURN
    vResult

This approach assumes you have a column [Is Working Day] where 1 = true, 0 = false.

[minishshah]

@DataInsights ,

Thank you so much for providing the solution. it worked for me.