Any way to use DAX to Generate a Custom Series

[JohnThomas]

Hi everyone...

I am trying to generate a series of numbers using DAX but I need to have better control over the values.  The desired outcome would be the following columns:

• Period: Starts at 1. Finish at 104 (Period Start/Finish is a sequence of numbers, 1, 2, 3, ... 104  but the overall goal would be to generate a series of dates.  Weekly in this example or 2 years of weeks. 
• Value: A decimal number with the following constraints:
  • Min Value: 500
  • Max Value: 10,500
  • Peak Period: 58 
  • Total Value: 540,800
• Goal is to get a total of 540,800 (Work Hours) spread between the start period and end period with the defined bell curve.
• The values should fit in a bell shaped curve with peak at period 58.  If the peak period is changed to 75, then the bell curve would be skewed to peak at period 75.

The resulting table would be:

Period Value

1          500

2          1000

3          1500

4          2500

...

57          9500

58          10500

59          9500

...

102         1500

103         1000

104         500

I have been working with GENERATESERIES but it doesn't allow any other parmaters beyond start/finish.  Likewise, RANDBETWEEN doesn't allow any parmaters to influence the numbers generated.  

Any bright ideas would be appreciated...

Thanks,

[Greg_Deckler · Accepted solution]

@JohnThomas Wouldn't you want to use ADDCOLUMNS around a GENERATESERIES and use NORM.DIST?

[JohnThomas]

@Greg_Deckler thanks for the suggestion.  I'll take a look at that and let you know what I find out.  I'll try to narrow down the problem to something that's a bit more specific with some sample data.

Thanks!

[Greg_Deckler]

@JohnThomas  or something like:

Table 2 = 
    ADDCOLUMNS(
        GENERATESERIES(1,104,1),
        "Value1",10500 - ABS(58 - [Value]) * 184
    )