Forum Discussion

markjdm_17's avatar
markjdm_17
New Member
6 years ago
Solved

Generate Series Based on Table (2 Columns)

Hi Team,    I am looking at generating a series of columns based on a table as per below.    Table:  Trading Name Open Date End Date Centre 1 2/05/2020 2/08/2020 Centre 2 24/08/202...
Need Help
Tutorial Requests
  • markjdm_17's avatar
    markjdm_17
    6 years ago

    Thanks for all the responses. I was able to figure this out by just generating the series with the above dax query I mentioned.  I then just created a rank column to get the sequence of dates. 

     

    Rank =
    VAR D = 'Result Table'[Value]
    VAR C = 'Result Table'[Label]
    RETURN
    CALCULATE(
    RANK.EQ(D, 'Result Table'[Value], ASC),
    FILTER(ALL('Result Table'), 'Result Table'[Label] = C)
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

markjdm_17 - If I am understanding this correctly, try this:

New Table =
SELECTCOLUMNS(
   FILTER(
         GENERATE(
                   'Table',
                   ADDCOLUMNS(
                     GENERATESERIES(MIN('Table'[Open Date]), MIN('Table'[End Date]), 1),
                     "Month",MONTH([Value])
                   )
          ),
         [Value] >= [Open Date]
         && [Value] <= [End Date]
   ),
  "Label", [Trading Name],
   "Value", [Value]
)