Forum Discussion
Produce monthly dataset by calculation
- 8 years ago
You can create a Calculated Table
From the Modelling Tab>>New Table
New Table = ADDCOLUMNS ( ADDCOLUMNS ( GENERATE ( SELECTCOLUMNS ( Table1, "Country", [Country] ), GENERATESERIES ( 1, 16 ) ), "Date", EOMONTH ( DATE ( 2017, 1, 1 ), [Value] - 2 ) + 1 ), "Capacity", VAR Mycalc = CALCULATE ( SUM ( Table1[Capacity] ), FILTER ( Table1, Table1[Country] = EARLIER ( [Country] ) && Table1[Start] <= [Date] ) ) RETURN IF ( ISBLANK ( Mycalc ), 0, mycalc ) ) - 8 years ago
Generateseries function
"Returns a single column table containing the values of an arithmetic series, that is, a sequence of values in which each differs from the preceding by a constant quantity. The name of the column returned is Value."
Since you needed 16 months (Jan 17 to April 18), I used GenerateSeries(1,16) to generate 16 rows
Then these were Crossjoined to each row of your existing table
Generateseries function
"Returns a single column table containing the values of an arithmetic series, that is, a sequence of values in which each differs from the preceding by a constant quantity. The name of the column returned is Value."
Since you needed 16 months (Jan 17 to April 18), I used GenerateSeries(1,16) to generate 16 rows
Then these were Crossjoined to each row of your existing table
All thebes
cocomy