Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 ?
Solved! Go to Solution.
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
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.
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.
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?
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