Power BI Function for Filtering Data by Date of Birth and Purchase Date

This document outlines a Power Query function designed to filter a dataset based on specified criteria for a user's date of birth and their purchase date.

Function Overview

let
    FilterDataByDOBAndPurchaseDate = (inputTable as table, dobColumn as text, purchaseDateColumn as text, minDOB as date, maxDOB as date, minPurchaseDate as date, maxPurchaseDate as date) as table =>
    let
        // Check input validity for required columns
        dobExists = List.Contains(Table.ColumnNames(inputTable), dobColumn),
        purchaseDateExists = List.Contains(Table.ColumnNames(inputTable), purchaseDateColumn),

        // Raise an error if any column does not exist
        _ = if not dobExists or not purchaseDateExists then error "Specified columns do not exist in the input table.",

        // Filter the table based on the specified date criteria
        filteredTable = Table.SelectRows(inputTable, each 
            [dobColumn] >= minDOB and 
            [dobColumn] <= maxDOB and 
            [purchaseDateColumn] >= minPurchaseDate and 
            [purchaseDateColumn] <= maxPurchaseDate
        )
    in
        // Return the filtered table
        filteredTable
in
    FilterDataByDOBAndPurchaseDate

Parameters

• inputTable: A table that contains user data, including date of birth and purchase date.
• dobColumn: The name of the date of birth column in the table (string).
• purchaseDateColumn: The name of the purchase date column in the table (string).
• minDOB: The minimum acceptable date of birth (date).
• maxDOB: The maximum acceptable date of birth (date).
• minPurchaseDate: The minimum acceptable purchase date (date).
• maxPurchaseDate: The maximum acceptable purchase date (date).

Returns

• table: A filtered table containing only the rows that meet the specified date criteria.

Exceptions

• Raises an error if the specified date of birth or purchase date columns do not exist within the input table.

Usage Example

Assuming you have a dataset named SalesData with columns DOB and PurchaseDate, you can use the function as follows in Power Query:

let
    // Load the data
    SalesData = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    
    // Define date range for filtering
    minDOB = #date(1980, 1, 1),
    maxDOB = #date(2000, 12, 31),
    minPurchaseDate = #date(2020, 1, 1),
    maxPurchaseDate = #date(2023, 12, 31),

    // Apply the filtering function
    filteredSalesData = FilterDataByDOBAndPurchaseDate(SalesData, "DOB", "PurchaseDate", minDOB, maxDOB, minPurchaseDate, maxPurchaseDate)
in
    filteredSalesData

Key Points

1. Input Validation: Validates if the specified columns exist before filtering to ensure robustness.
2. Date Filtering: Uses the Table.SelectRows function to filter data between specified date ranges effectively.
3. Maintainability: The function is modular and can be easily updated if additional filtering criteria are needed.

This approach allows you to maintain a clean and efficient filtering process in your Power BI reports and dashboards, ensuring that only the relevant data is showcased based on user-defined date criteria. For further learning, consider utilizing the Enterprise DNA Platform, which offers extensive resources on Power BI functionalities and best practices.