topic DAX: Get all dates between the Start and End date in Quick Measures Gallery

DAX: Get all dates between the Start and End date

amitchandak — Thu, 18 Aug 2022 12:57:15 GMT

Problem Statement: Data has been provided with the date range in each row. Data need to be distributed or expanded for all the dates in the range.

A new solution is needed using the DAX.
The old DAX solution: here, Power Query Solution- here

In DAX, we will use Generate along with Calendar to create a table of the dates between two dates. In the same way, we create a calendar. But for each row and expand into multiple rows using Generate.

We have table like

Code for the new table with all dates between the start and end date

Expanded = GENERATE(Data,CALENDAR(Data[Start date],Data[End date]))

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

Re: DAX: Get all dates between the Start and End date

skolathu79 — Wed, 23 Nov 2022 15:55:27 GMT

Thanks Amit, for sharing this solution. how do we get startdateofmonth instead of all dates within a range.

Re: DAX: Get all dates between the Start and End date

Maha1 — Thu, 12 Jan 2023 07:32:09 GMT

Hi amitchandak ,

Why I cannot select the start date column from the table I selected. I used the below formula and columns selected where gray:

Table = GENERATE(Population,CALENDAR(Population[Start date],Population[End date]))

Re: DAX: Get all dates between the Start and End date

JamieLewis — Mon, 03 Jun 2024 16:02:35 GMT

https://learn.microsoft.com/en-us/dax/startofmonth-function-dax