Forum Discussion

ibrahimsharaf's avatar
ibrahimsharaf
Microsoft Employee
10 years ago

Getting all dates between 2 dates

Hello guys, I have 2 columns named, start date and end date, I want to create a column which contains all the dates between the after mentioned dates, for example      
Need Help
Show and Tell
Tips and Tricks
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi ibrahimsharaf

 

Here is a DAX based solution.  This is a calculated table and just replace the Table3 with the name of your table.

 

New Table = 
            SELECTCOLUMNS(
                FILTER(
                    CROSSJOIN('Table3',CALENDARAUTO()),
                   'Table3'[EndDate] >= [Date]
                   && 'Table3'[StartDate] <= [Date]
                   ),
                   "Date",[Date])
Ashish_Mathur's avatar
Ashish_Mathur
Super User
9 years ago

Hi Phil,

 

If i am using the PowerPivot in Excel, where exactly do i have to write this formula.  How does one generate a table in the PowerPivot?

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Ashish_Mathur

     

    Sorry, that code is more for Power BI Desktop or SSAS Tabular where you can create calculated tables in DAX.  I recommend you follow ImkeF always excellent suggestions :)

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      9 years ago

      Thank you.