DAX: Get all dates between the Start and End date 06-03-2024 09:02 AM

[amitchandak]

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

[JamieLewis]

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

[Maha1]

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

[skolathu79]

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