DAX: Get all dates between the Start and End date
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
3 Replies
Thanks Amit, for sharing this solution. how do we get startdateofmonth instead of all dates within a range.
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]))
