How to Get a List of Dates Between Two Date Columns in Power BI

Introduction

In data analysis and reporting, there are often situations where you need to generate a list of dates between a start date and an end date. This can be particularly useful for tracking trends, analyzing historical data, or visualizing data over a specific time period. This tutorial will guide you through the process of creating a custom column in Power BI to generate a list of dates between two date columns.

The Problem

Imagine you have a dataset with two date columns: Starting Date and Ending Date. Your task is to create a list of dates that fall between these two dates for each row in your dataset. If the Ending Date is missing or contains null values, you want to substitute it with today's date to ensure accurate date range calculations.

Step-by-Step Guide

Step 1: Handle Null Values in Ending Date

Before generating the list of dates, ensure that the Ending Date column is prepared:

• If the Ending Date column contains null values, replace them with today’s date using the following formula:

if [Ending Date] = null then Date.From(DateTime.LocalNow()) else [Ending Date]

We can also just replace the current column’s null values using this code:

= Table.ReplaceValue(#"Changed Type with Locale",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Promo End date"})

Step 2: Create a Custom Column for List of Dates

Now, create a new custom column to generate the list of dates between Starting Date and Ending Date (or Ending Date adjusted for null values):

• Add a custom column and paste the following formula:

let

    Alldaysnumber = {Number.From([Starting Date])..Number.From([Ending Date Latest])},

    listdays = List.Transform(Alldaysnumber, each Date.From(_))

in

    listdays

Step 3: Expand the Custom Column

• Click on the expand icon (top-right of the new custom column) and select "Expand to New Rows."

Conclusion

Generating a list of dates between two date columns in Power BI allows for flexible date analysis and reporting capabilities. By following these steps, you can effectively handle date ranges within your datasets and enhance your data visualization projects.

Keywords:

• Power BI list of dates between two columns

• Date range calculation in Power BI

• Generate dates between two dates

• Power BI custom column dates

• Power BI tutorial on dates

Meta Description:

Learn how to generate a list of dates between two date columns in Power BI. This step-by-step tutorial will help you manage date ranges and improve data analysis efficiency in your reports.