Generate Series Based on Table (2 Columns)

[markjdm_17]

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/2020	24/11/2020

Results Table: 

Trading Name	Date	Month
Centre 1	2/05/2020	1
Centre 1	2/06/2020	2
Centre 1	2/07/2020	3
Centre 1	2/08/2020	4
Centre 2	24/08/2020	1
Centre 2	24/09/2020	2
Centre 2	24/10/2020	3
Centre 2	24/11/2020	4

Now I was able to generate the table using the below guide but can't figure out how to add the series (Month) column. 

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

https://community.powerbi.com/t5/Desktop/Generate-series-of-numbers-based-on-other-table-values/m-p/...

Cheers,
Mark

[Greg_Deckler]

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

[amitchandak]

@markjdm_17 , refer to my HR blog. Seems that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[markjdm_17 · Accepted solution]

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)

)

[Icey]

Hi @markjdm_17 ,

Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly. Your contribution is highly appreciated.

Best Regards,

Icey