Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
marcushart12
Frequent Visitor

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

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 ? 

1 ACCEPTED SOLUTION
gmsamborn
Super User
Super User

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

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

View solution in original post

4 REPLIES 4
marcushart12
Frequent Visitor

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. 

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.

 



Proud to be a Super User!

daxformatter.com makes life EASIER!
gmsamborn
Super User
Super User

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?



Proud to be a Super User!

daxformatter.com makes life EASIER!
gmsamborn
Super User
Super User

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

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!