How to get Friday's transaction date to show on Monday's report

[marcushart12]

Hello, we are trying to automate our daily report. On Monday when we run the report, we would like for the report to auto filter on the transaction date from the previous Friday.

To make a long story short, we are not including weekends.

Mon should show Fri trans date

Tuesday should show Mon trans date

Wednesday should show Tue trans date

Thursday should show Wed trans date and etc..... (No Sat or Sun transaction date)

What is the best wat we could accomplish this ? 

[marcushart12]

I forgot to mention that this report is in a workspace and is scheduled to refresh Mon thru Friday via a connection to teradata. When I open the report on today, I would like for the transaction date filter to be set to the previous working day. If today was Monday and I am viewing the report, would like for the report to be filtered on Friday's tranaction date. 

[gmsamborn]

Hi @marcushart12 

That changes things quite a bit.

I've looked around at various solutions for a "default date" for a slicer but I can't see how it could be done with "previous working day".

Hopefully someone else will be able to help.

[gmsamborn]

Hi @marcushart12 

After rereading I realized you wanted to see the transaction date instead the transactions.

Prev Workday = 
    CALCULATE(
        MIN( 'Date'[Date] ),
        ALL( 'Date' ),
        'Date'[Working Day Number] = MAX( 'Date'[Working Day Number] ) - 1
    )

Will this help?

[gmsamborn · Accepted solution]

Hi @marcushart12 

I first created a date table.  (or your date table would work if you have one)

Date = CALENDAR( DATE( 2022, 1, 1 ), DATE( 2025, 12, 31 ) )

Then I added 2 calculated columns to the Date table:

Working Day = 
    IF(
        NOT WEEKDAY( 'Date'[Date], 2 ) IN { 6, 7 },
        TRUE(),
        FALSE()
    )


Working Day Number = 
RANKX(
    FILTER(
        'Date',
        'Date'[Working Day]
    ),
    'Date'[Date],
    ,
    ASC
) - NOT 'Date'[Working Day]

And then I added 2 measures:

Sales = SUM( 'Sales'[Sales] )


Prev Workday Sales = 
VAR _WorkingDayNum = MAX( 'Date'[Working Day Number] )
VAR _Day = WEEKDAY( MAX( 'Date'[Date] ), 3 )
VAR _Result =
    SWITCH(
        TRUE(),
        _Day IN { 5, 6 },
            0,
            CALCULATE(
                [Sales], 
                ALL( 'Date' ),
                'Date'[Working Day Number] = _WorkingDayNum - 1
            ) + 0
    )
RETURN
    _Result

Let me know if you have any questions.

How to get Friday's transaction date to show on Monday's report.pbix