How to Implement Rolling and Fixed Period Flags for Date Intelligence in Power BI

How to Create Rolling and Fixed Period Date Range Buckets or Flags in Power BI Date Table

Scenario:

Suppose we want to generate Rolling Period Flags and Fixed Period Flags in a Calendar table based on the Today() date. Let’s assume today is April 10, 2026.

In reporting and analytics, dates are the backbone of the comparison of trends, and insights. The business users often need to filter data by relative time windows (like “last 30 days”) or by absolute calendar periods (like “Q1 2026”).
The Rolling and Fixed Period Flags become helpful to classify each date in the calendar table into meaningful categories that can be directly used in reports, dashboards, and KPIs.

Rolling Period Date Range Flags: 

Rolling periods are dynamic, relative to today. Rolling flags are perfect for trend analysis and monitoring ongoing activity. They move forward continuously as time progresses. This means the definition of “Last 7 Days” or “Last 1 Month” changes every day.

Example (with April 10, 2026 as Today):

• April 10, 2026: Today
• April 9, 2026: Previous Day
• April 3–10, 2026: Last 7 Days
• March 10–April 10, 2026: Last 1 Month
• January–March 2026: Last 1 Quarter
• April 2025–April 2026: Last 1 Year
• Any date older than April 10, 2025: Historical
• Any date after April 10, 2026: Future

How Rolling Period Date Range flags helps in Business Reporting:

Rolling flags are perfect for trend analysis and monitoring ongoing activity because they always move relative to today. In a sales context, this means:

• Sales in the last 30 days: Helps track short‑term performance and spot momentum or slowdowns.
• Sales in the past 7 days: Useful for weekly monitoring, promotions, or campaign impact.
• Sales in the last quarter: Shows how the business is performing in the most recent 90‑day window, regardless of calendar boundaries.
• Sales in the last year: Provides a rolling year‑to‑date view, ideal for understanding long‑term growth or decline.

Fixed Period Date Range Flags:  

Fixed periods are anchored to established calendar boundaries. They classify dates into complete units such as weeks, months, quarters, or years. Unlike rolling periods, which shift continuously day by day, fixed periods remain aligned with the defined calendar structure, ensuring consistency for reporting and comparisons.

Example (with April 10, 2026 as Today):

• April 10, 2026: Today
• April 11, 2026: Next Day
• April 6–12, 2026: Current Week
• March 2026: Previous Month
• Q2 2026: Current Quarter
• Q1 2026: Previous Quarter
• 2026: Current Year
• 2025: Previous Year

How Fixed Period Date Range flags helps in Business Reporting:

Fixed flags are anchored to calendar boundaries, which makes them essential for structured comparisons. In sales reporting, this means:

• Current Month vs Previous Month: Compare March sales with April sales to measure month‑over‑month growth.
• Current Quarter vs Previous Quarter: Evaluate Q2 sales against Q1 to understand quarterly performance.
• Current Year vs Previous Year: Assess year‑over‑year growth, a key metric for stakeholders and investors.
• Current Week vs Previous Week: Useful for weekly sales dashboards aligned with business calendars.

Now we can create / define a Calendar table with these Rolling and Fixed Period flags using the below Power Query logic:

let

    /* ================================

       Define Date Range

       ================================ */

    _Today        = Date.From(DateTime.LocalNow()),   /* Current date */

    _CurrentYear  = Date.Year(_Today),                       /* Current Year */

    _PreviousYear = _CurrentYear - 1,                          /* Previous Year */

    _MinDate      = #date(_PreviousYear, 1, 1),             /* Start Date: Jan 1 of Previous Year */

    _MaxDate      = #date(_CurrentYear, 12, 31),          /* End Date: Dec 31 of Current Year */

    /* ================================

       Generate List of Dates

       ================================ */

    _DateList = List.Dates(

        _MinDate,

        Duration.Days(_MaxDate - _MinDate) + 1,

        #duration(1,0,0,0)

        ),

    _CalendarBase = Table.TransformColumnTypes(

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

        {{"Date", type date}}

        ),

    /* ================================

       Standard Date Attributes

       ================================ */

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

    _WeekOfYear     = Table.AddColumn(_DayOfWeekNum, "Week_Of_Year", each Date.WeekOfYear([Date]), Int64.Type),

    _DayOfWeekName  = Table.AddColumn(_WeekOfYear, "Week_Day_Name", each Text.Start(Date.DayOfWeekName([Date]),3), type text),

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

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

    _Quarter        = Table.AddColumn(_MonthName, "Qtr_No", 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_Key", each [Year] * 100 + Date.Month([Date]), Int64.Type),

    /* ================================

       Rolling Period Classification:

       Past → Present → Future

       ================================ */

    _RollingPeriodFlag = Table.AddColumn(_YearMonthKey, "Rolling_Period_Flag", each 

        if [Date] = _Today then "Today"

        else if [Date] = Date.AddDays(_Today, -1) then "Previous Day"

        else if [Date] >= Date.AddDays(_Today, -7) and [Date] <= _Today then "Last 7 Days"

        else if [Date] >= Date.AddWeeks(_Today, -1) and [Date] <= _Today then "Last 1 Week"

        else if [Date] >= Date.AddMonths(_Today, -1) and [Date] <= _Today then "Last 1 Month"

        else if [Date] >= Date.AddQuarters(_Today, -1) and [Date] <= _Today then "Last 1 Quarter"

        else if [Date] >= Date.AddYears(_Today, -1) and [Date] <= _Today then "Last 1 Year"

        else if [Date] > _Today and [Date] <= Date.AddWeeks(_Today, 1) then "Next Week"

        else if [Date] > _Today and [Date] <= Date.AddMonths(_Today, 1) then "Next Month"

        else if [Date] > _Today and [Date] <= Date.AddQuarters(_Today, 1) then "Next Quarter"

        else if [Date] > _Today and [Date] <= Date.AddYears(_Today, 1) then "Next Year"

        else if [Date] > Date.AddYears(_Today, 1) then "Future"

        else "Historical"

    , type text),

    /* ================================

       Fixed Period Classification:

       Day → Week → Month → Quarter → Year

       ================================ */

    _FixedPeriodFlag = Table.AddColumn(_RollingPeriodFlag, "Fixed_Period_Flag", each 

        let

            _CurrentWeekNo   = Date.WeekOfYear(_Today),

            _CurrentMonthNo  = Date.Month(_Today),

            _CurrentQtrNo    = Date.QuarterOfYear(_Today),

            _CurrentYearNo   = Date.Year(_Today)

        in

            if [Date] = _Today then "Today"

            else if [Date] = Date.AddDays(_Today, 1) then "Next Day"

            else if [Week_Of_Year] = _CurrentWeekNo and [Year] = _CurrentYearNo then "Current Week"

            else if [Week_Of_Year] = _CurrentWeekNo - 1 and [Year] = _CurrentYearNo then "Previous Week"

            else if [Week_Of_Year] = _CurrentWeekNo + 1 and [Year] = _CurrentYearNo then "Next Week"

            else if [Month_No] = _CurrentMonthNo and [Year] = _CurrentYearNo then "Current Month"

            else if [Month_No] = _CurrentMonthNo - 1 and [Year] = _CurrentYearNo then "Previous Month"

            else if [Month_No] = _CurrentMonthNo + 1 and [Year] = _CurrentYearNo then "Next Month"

            else if Date.QuarterOfYear([Date]) = _CurrentQtrNo and [Year] = _CurrentYearNo then "Current Quarter"

            else if Date.QuarterOfYear([Date]) = _CurrentQtrNo - 1 and [Year] = _CurrentYearNo then "Previous Quarter"

            else if Date.QuarterOfYear([Date]) = _CurrentQtrNo + 1 and [Year] = _CurrentYearNo then "Next Quarter"

            else if [Year] = _CurrentYearNo then "Current Year"

            else if [Year] = _PreviousYear then "Previous Year"

            else if [Year] = _CurrentYearNo + 1 then "Next Year"

            else "Other_Periods"

    , type text),

    /* ================================

       ToDate Buckets (WTD, MTD, QTD, YTD)

       ================================ */

    _ToDateBuckets = Table.AddColumn(_FixedPeriodFlag, "ToDate_Ranges", each

        let

            _CurrentWeekNo   = Date.WeekOfYear(_Today),

            _CurrentMonthNo  = Date.Month(_Today),

            _CurrentQtrNo    = Date.QuarterOfYear(_Today),

            _CurrentYearNo   = Date.Year(_Today)

        in

            if [Year] = _CurrentYearNo and [Week_Of_Year] = _CurrentWeekNo and [Date] <= _Today then "WeekToDate"

            else if [Year] = _CurrentYearNo and [Month_No] = _CurrentMonthNo and [Date] <= _Today then "MonthToDate"

            else if [Year] = _CurrentYearNo and Date.QuarterOfYear([Date]) = _CurrentQtrNo and [Date] <= _Today then "QuarterToDate"

            else if [Year] = _CurrentYearNo and [Date] <= _Today then "YearToDate"

            else "Other_Periods"

    , type text)

in

    _ToDateBuckets

#Result:

Notes:
We can also create these Date Range Buckets or Flags using the below DAX Logics. You can also customize these Date Range buckets as per your requirements:

Rolling_Period_Flag1 =

VAR _TodayDate = TODAY()

RETURN

SWITCH (

    TRUE(),

    dim_Calendar[Date] = _TodayDate, "Today",

    dim_Calendar[Date] = _TodayDate - 1, "Previous Day",

    dim_Calendar[Date] >= _TodayDate - 7 &&

        dim_Calendar[Date] <= _TodayDate, "Last 7 Days",

    dim_Calendar[Date] >= _TodayDate - 7 &&

        dim_Calendar[Date] <= _TodayDate, "Last 1 Week",

    dim_Calendar[Date] >= EDATE(_TodayDate, -1) &&

        dim_Calendar[Date] <= _TodayDate, "Last 1 Month",

    dim_Calendar[Date] >= EDATE(_TodayDate, -3) &&

        dim_Calendar[Date] <= _TodayDate, "Last 1 Quarter",

    dim_Calendar[Date] >= EDATE(_TodayDate, -12) &&

        dim_Calendar[Date] <= _TodayDate, "Last 1 Year",

    dim_Calendar[Date] > _TodayDate &&

        dim_Calendar[Date] <= _TodayDate + 7, "Next Week",

    dim_Calendar[Date] > _TodayDate &&

        dim_Calendar[Date] <= EDATE(_TodayDate, 1), "Next Month",

    dim_Calendar[Date] > _TodayDate &&

        dim_Calendar[Date] <= EDATE(_TodayDate, 3), "Next Quarter",

    dim_Calendar[Date] > _TodayDate &&

        dim_Calendar[Date] <= EDATE(_TodayDate, 12), "Next Year",

    dim_Calendar[Date] > EDATE(_TodayDate, 12), "Future",

    "Historical"

    )

Fixed_Period_Flag1 =

VAR _TodayDate    = TODAY()

VAR _CurrentYear  = YEAR(_TodayDate)

VAR _PrevYear     = _CurrentYear - 1

VAR _CurrentMonth = MONTH(_TodayDate)

VAR _CurrentQTR   = QUARTER(_TodayDate)

VAR _CurrentWeek  = WEEKNUM(_TodayDate, 1)   -- 1 = Week starts Sunday

RETURN

SWITCH (

    TRUE(),

    dim_Calendar[Date] = _TodayDate, "Today",

    dim_Calendar[Date] = _TodayDate + 1, "Next Day",

    dim_Calendar[Week_Of_Year] = _CurrentWeek &&

        dim_Calendar[Year] = _CurrentYear, "Current Week",

    dim_Calendar[Week_Of_Year] = _CurrentWeek - 1 &&

        dim_Calendar[Year] = _CurrentYear, "Previous Week",

    dim_Calendar[Week_Of_Year] = _CurrentWeek + 1 &&

        dim_Calendar[Year] = _CurrentYear, "Next Week",

    dim_Calendar[Month_No] = _CurrentMonth &&

        dim_Calendar[Year] = _CurrentYear, "Current Month",

    dim_Calendar[Month_No] = _CurrentMonth - 1 &&

        dim_Calendar[Year] = _CurrentYear, "Previous Month",

    dim_Calendar[Month_No] = _CurrentMonth + 1 &&

        dim_Calendar[Year] = _CurrentYear, "Next Month",

    dim_Calendar[Qtr_No] = "Q" & _CurrentQTR &&

        dim_Calendar[Year] = _CurrentYear, "Current Quarter",

    dim_Calendar[Qtr_No] = "Q" & (_CurrentQTR - 1) &&

        dim_Calendar[Year] = _CurrentYear, "Previous Quarter",

    dim_Calendar[Qtr_No] = "Q" & (_CurrentQTR + 1) &&

        dim_Calendar[Year] = _CurrentYear, "Next Quarter",

    dim_Calendar[Year] = _CurrentYear, "Current Year",

    dim_Calendar[Year] = _PrevYear, "Previous Year",

    dim_Calendar[Year] = _CurrentYear + 1, "Next Year",

    "Other_Periods"

    )

ToDate_Ranges1 =

VAR _TodayDate    = TODAY()

VAR _CurrentYear  = YEAR(_TodayDate)

VAR _CurrentMonth = MONTH(_TodayDate)

VAR _CurrentQTR   = QUARTER(_TodayDate)

VAR _CurrentWeek  = WEEKNUM(_TodayDate, 1)

RETURN

SWITCH (

    TRUE(),

    dim_Calendar[Year] = _CurrentYear &&

    dim_Calendar[Week_Of_Year] = _CurrentWeek &&

    dim_Calendar[Date] <= _TodayDate, "WeekToDate",

    dim_Calendar[Year] = _CurrentYear &&

    dim_Calendar[Month_No] = _CurrentMonth &&

    dim_Calendar[Date] <= _TodayDate, "MonthToDate",

    dim_Calendar[Year] = _CurrentYear &&

    dim_Calendar[Qtr_No] = "Q" & _CurrentQTR &&

    dim_Calendar[Date] <= _TodayDate, "QuarterToDate",

    dim_Calendar[Year] = _CurrentYear &&

    dim_Calendar[Date] <= _TodayDate, "YearToDate",

    "Other_Periods"

    )

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

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

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
--------------------------------------------------------------------------------------------------------

How to track Entity Load and Readiness with Pipeline Execution Logs in Power BI

How to use Power BI DAX Logics to track Entity Load and Readiness Status with Pipeline Execution Logs

Scenario:
In modern data platforms, it is often necessary to track whether critical business entities have been successfully processed in the latest pipeline execution. 

Suppose we want to monitor the Load Status and Readiness Status of a defined list of entities such as:
dim_Customer, dim_Product, fact_Orders, fact_Inventory, dim_Supplier, fact_Payments, and fact_Order_Returns.

We can achieve this scenario by setting up two simple tables as per below. 

ref_Entities_to_Track:
It is a reference table which lists all the entities we expect to load in every pipeline run.

fact_Pipeline_Execution_Log:
It is a fact table, contains the details of each run, including the run date, pipeline name, entity key, entity name, pipeline run status, and entity refresh status.

Notes:  Assume that these tables do not have a direct relationship in the Data Model.

By comparing the reference table with the pipeline execution log for the latest run date (either today or the most recent available date), we can quickly identify the status of each entity. 

This shows whether an entity was successfully updated, failed to load, or was missing. 

Using the below measures, we can also derive a simple flag (1/0) for each entity to highlight its presence in the latest run.

Entity Availability Flag =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    COUNTROWS(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            )

        ) > 0,

        1,

        0

    )

Notes:
1: Indicates the Entity exists in the latest run.
0: Indicates the Entity is missing in the latest run.

We can use the below measure to count the no. of entities Missing in the latest refresh:

Missing Entities Count =

VAR _LatestRunDate =

    CALCULATE ( MAX ( fact_Pipeline_Execution_Log[Pipeline_Run_Date] ) )

VAR _AllEntities =

    VALUES ( ref_Entities_to_Track[Entity_Key] )

VAR _LoggedEntities =

    CALCULATETABLE (

        VALUES ( fact_Pipeline_Execution_Log[Entity_Key] ),

        fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate

    )

VAR _MissingEntities =

    EXCEPT ( _AllEntities, _LoggedEntities )

RETURN

COUNTROWS ( _MissingEntities )

For entities included in the latest run, we calculate the following measures to track their status:

• Entity_Refresh_Status: to show whether the entity had new updates, no updates, or a load failure.
• Pipeline_Run_Status: to show whether the pipeline succeeded, failed, or was missing.

Entity Refresh Status =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    [Entity Availability Flag] = 0,

    "Missing in Data Load",

    MAXX(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            ),

        fact_Pipeline_Execution_Log[Entity_Refresh_Status]

        )

    )

Pipeline Run Status =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    [Entity Availability Flag] = 0,

    "Not Avaiable",

    MAXX(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            ),

        fact_Pipeline_Execution_Log[Pipeline_Run_Status]

        )

    )

We can find the Last Refresh date of each Entity using the below logic:

Entity Last_Refresh_Date =

VAR _EntityName = MAX ( ref_Entities_to_Track[Entity_Name] )

RETURN

CALCULATE (

    MAX ( fact_Pipeline_Execution_Log[Entity_Refresh_Date] ),

    FILTER (

            ALL(fact_Pipeline_Execution_Log),

        fact_Pipeline_Execution_Log[Entity_Name] = _EntityName

    )

)

Result:

Notes:

We can identify the latest pipeline run date and assign a flag value of 1 or 0 using the below measures. 

By setting the [Latest RunDate Flag] measure to 1 for the most recent run date, Slicers in the Power BI can automatically default to that execution.
This ensures stakeholders always see the latest pipeline health information without manual effort.

Latest RunDate =

CALCULATE(

    MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]),

    ALL(fact_Pipeline_Execution_Log)

    )

Latest RunDate Flag =

VAR _MaxRunDate =

    CALCULATE (

        MAX ( fact_Pipeline_Execution_Log[Pipeline_Run_Date] ),

        ALL ( fact_Pipeline_Execution_Log )

    )

VAR _Today = TODAY()

VAR _LatestRunDate = MIN ( _MaxRunDate, _Today )

RETURN

IF (

    SELECTEDVALUE ( fact_Pipeline_Execution_Log[Pipeline_Run_Date] ) = _LatestRunDate,

        1,

        0

    )

Business Value Creation / Insights:

From the above scenario, technical teams can derive practical insights into pipeline execution and data readiness. Dashboards in Power BI can highlight:

• Entities that were successfully refreshed with new data.
• Entities that had no changes.
• Entities that failed to load.
• Entities that were missing from the latest run.

This helps data engineers and support teams quickly detect issues, validate pipeline health, and take corrective actions. By automating this monitoring, they reduce manual checks, improve reliability, and ensure that downstream business users always receive consistent and trustworthy data.

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

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