Forum Discussion

markjdm_17's avatar
markjdm_17
New Member
5 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 NameOpen DateEnd Date
Centre 12/05/20202/08/2020
Centre 224/08/202024/11/2020

 

Results Table: 

Trading NameDateMonth
Centre 12/05/20201
Centre 12/06/20202
Centre 12/07/20203
Centre 12/08/20204
Centre 224/08/20201
Centre 224/09/20202
Centre 224/10/20203
Centre 224/11/20204

 

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/968384#M463731

 

Cheers,
Mark

Need Help
Tutorial Requests
  • markjdm_17's avatar
    markjdm_17
    5 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)
    )

4 Replies

    • markjdm_17's avatar
      markjdm_17
      New Member
      5 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)
      )
      • Icey's avatar
        Icey
        Community Support
        5 years ago

        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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 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]
)