Forum Discussion
Produce monthly dataset by calculation
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 ) )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
Hi Zubair,
Thank you for helping me out on this.
I would like to add branch by using addcolumns. I tried to insert ADDCOLUMNS(Table1,"Branch",[Branch])
into your calculation but received various error messages.(ie. Branch already exist.)
Where should I insert Branch in your DAX fomular and how?
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 )
)
All the best,
cocomy