Forum Discussion

ibrahimsharaf's avatar
ibrahimsharaf
Icon for Microsoft Employee rankMicrosoft 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Super User rankSuper 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
      Icon for Microsoft Employee rankMicrosoft 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
        Icon for Super User rankSuper User
        9 years ago

        Thank you.

  • ctedesco3307's avatar
    ctedesco3307
    Icon for Resolver II rankResolver II
    4 years ago

    This above solution is great - is there a way to get more columns in the calculated table from 'Table 3'  so it's not just the one column? - Thank you 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Share some data and show the expected result.

    • ctedesco3307's avatar
      ctedesco3307
      Icon for Resolver II rankResolver II
      4 years ago

      Can soemone share the syntax to get those extra columns? this works perfect but I need those extra columns as well. Thank you.