How to set default Reporting Date to Last Friday Date in Power BI

How to set default Reporting Date to a Custom Date in Power BI

Scenario:

When building reports in Power BI, one common requirement is to set a default reporting date that adapts to business rules. For example, sales teams often want the report to show the most relevant date automatically without manual Date selection/adjustments. 

In this article, we’ll walk through how to reset the default reporting date based on below specific conditions:

• If today date falls on Saturday, Sunday, or Monday, then the reporting date should default to last Friday date. Otherwise, the reporting date should default to Yesterday’s date.
• If there is no sales data for the chosen date, fallback to the maximum available sales date from fact_Sales_Sample[Order_Date].

Data Model: Let's say we have the below tables in the Data Model, where dim_Calendar is connected with fact_Sales_Sample as per below:

dim_Calendar[Date] (1): (M) fact_Sales_Sample[Order_Date]

dim_Calendar: The date dimension table, from which we use the Date Slicer.

let

    // Define Min and Max Dates

    Min_Date = #date(2026, 3, 1),

    Max_Date = #date(2026, 3, 31),

    // Generate list of dates

    DateList = List.Dates(

        Min_Date,

        Duration.Days(Max_Date - Min_Date) + 1,

        #duration(1,0,0,0)

    ),

    _DateTable = Table.TransformColumnTypes(

        Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"},type table [Date = Date.Type]),

        {{"Date", type date}}

        ),

    _WeekDayNo = Table.AddColumn(_DateTable, "Week_Day_No", each Date.DayOfWeek([Date], Day.Sunday) + 1, Int64.Type),

    _WeekNum = Table.AddColumn(_WeekDayNo, "Week_No_Year", each Date.WeekOfYear([Date]), Int64.Type),

    _WeekDay = Table.AddColumn(_WeekNum, "Week_Day", each Text.Start(Date.DayOfWeekName([Date]),3), type text),

 _Month_No = Table.AddColumn(_WeekDay, "Month_No", each Date.Month([Date]), Int64.Type),

    _Month_Name = Table.AddColumn(_Month_No, "Month_Name", each Date.MonthName([Date]), type text),

    _Quarter = Table.AddColumn(_Month_Name, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),    

    _Year = Table.AddColumn(_Quarter, "Year", each Date.Year([Date]), Int64.Type),

    _YearMonthKey = Table.AddColumn(_Year,"Year_Month_SortKey", each [Year] * 100 + Date.Month([Date]), Int64.Type)

in

    _YearMonthKey

fact_Sales_Sample: It is a fact table contains the sample Sales transactions with Order_Date and Gross_Revenue.

let

    Source = #table(

        type table [

            Order_Id = Int64.Type,

            Order_Date = Date.Type,

            Product_Name = Text.Type,

            Gross_Revenue = Int64.Type

            ],

        {

            {1,#date(2026,3,20),"Radio",1200},

            {2,#date(2026,3,20),"Television",800},

            {3,#date(2026,3,20),"Laptop",500},

            {4,#date(2026,3,21),"Radio",1500},

            {5,#date(2026,3,21),"Television",700},

            {6,#date(2026,3,21),"Laptop",400},

            {7,#date(2026,3,22),"Radio",null},

            {8,#date(2026,3,22),"Television",null},

            {9,#date(2026,3,22),"Laptop",null},

            {10,#date(2026,3,23),"Radio",2000},

            {11,#date(2026,3,23),"Television",900},

            {12,#date(2026,3,23),"Laptop",600},

            {13,#date(2026,3,24),"Radio",1800},

            {14,#date(2026,3,24),"Television",1100},

            {15,#date(2026,3,24),"Laptop",500},

            {16,#date(2026,3,25),"Radio",null},

            {17,#date(2026,3,25),"Television",null},

            {18,#date(2026,3,25),"Laptop",null},

            {19,#date(2026,3,26),"Radio",2200},

            {20,#date(2026,3,26),"Television",1000},

            {21,#date(2026,3,26),"Laptop",700},

            {22,#date(2026,3,27),"Radio",1600},

            {23,#date(2026,3,27),"Television",1200},

            {24,#date(2026,3,27),"Laptop",900},

            {25,#date(2026,3,28),"Radio",1400},

            {26,#date(2026,3,28),"Television",800},

            {27,#date(2026,3,28),"Laptop",600}

        }

    )

in

    Source

Measures:
We can use the below DAX Measure, which returns the Reporting Date as per the conditions as mentioned in the above Scenario.

Reporting Date =

VAR _TodayDate    = [Today Date] --TODAY()

VAR _TodayWeekDay = WEEKDAY(_TodayDate,2)   /* 1=Mon … 7=Sun */

/* Max_SaleDate: Last available Sales date (ignores blanks) */

VAR _Max_SaleDate =

    CALCULATE(

        MAX(fact_Sales_Sample[Order_Date]),

        FILTER(

            ALL(fact_Sales_Sample),

            NOT ISBLANK(fact_Sales_Sample[Gross_Revenue])

        )

    )

/* Target_Date: Last Friday if Today is Sat/Sun/Mon, else Yesterday Date */

VAR _Target_Date =

    IF(

        _TodayWeekDay IN {6,7,1},

        CALCULATE(

            MAX(dim_Calendar[Date]),

            FILTER(

                ALL(dim_Calendar),

                dim_Calendar[Date] < _TodayDate &&

                WEEKDAY(dim_Calendar[Date],2) = 5

            )

        ),

        _TodayDate - 1

    )

/* Check whether there is Sales on Target_Date */

VAR _Sales_Check =

    CALCULATE(

        SUM(fact_Sales_Sample[Gross_Revenue]),

        TREATAS({_Target_Date}, dim_Calendar[Date])

    )

/* Report_Date: Take only the minimum valid date */

VAR _Report_Date =

    IF(NOT ISBLANK(_Sales_Check),

            _Target_Date,

            COALESCE(_Target_Date, _Max_SaleDate)

        )

VAR _Final_Report_Date = MIN(_Report_Date, _Max_SaleDate)

RETURN

IF(

    CONTAINS(

        VALUES(dim_Calendar[Date]),

        dim_Calendar[Date], _Final_Report_Date

    ),

    _Final_Report_Date,

    BLANK()

 )

We can use the below Measure to return the Week Day of the Reporting Date:

Reporting WeekDay =

IF(

    CONTAINS(

        VALUES(dim_Calendar[Date]),

        dim_Calendar[Date], [Reporting Date]

    ),

    FORMAT([Reporting Date],"ddd"),

    BLANK()

 )

We can use the below Measure to set the Flag 1 or 0 based on the Reporting Date. This will be used as Visual Level filter as 1 for Date Slicer from dim_Calendar[Date].

Reporting Date Flag =

IF(

    MAX(fact_Sales_Sample[Order_Date]) = [Reporting Date],

    1, 0

    )

Result:
In the following Result, the [Report Date] Slicer is taken from dim_Calendar[Date] with visual level filter as Reporting Date Flag = 1.
Let's say Today () date is 23-03-2026 is Monday, then Reporting Date is set to last Friday date as 20-03-2026 as per the logic.

It works in the same way if Today () date falls on Saturday and Sunday as well.

In the below case the Today () date is 24-03-2026 is Tuesday, hence Reporting Date is set to the previous day, Monday date as 23-03-2026.

In the below case, though Today () date is 02-04-2026 is Thursday, the Reporting Date is set to the Max Sale date as 28-03-2026 instead of previous day, as there is no data available in the fact Sales table.

Key Takeaways:

• Automating the reporting date improves user experience by reducing manual selection of dates.
• The logic ensures business rules are respected (Friday reporting for Weekends and for the Mondays).
• Fallback to maximum Sales date guarantees that reports always show valid data.

Note: You can customize the above logics as to align with your reporting requirements.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------