Forum Discussion
Produce monthly dataset by calculation
Hi All,
I have a list of Branch capacity with start and end date. (left)
I want to make monthly list by country with total capacity.
Is there any way I can make by calculation?(right)
All the best,
cocomy
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
7 Replies
- Zubair_MuhammadCommunity Champion
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 ) )- Zubair_MuhammadCommunity Champion
- cocomyResolver I
Hi Zubair,
Thank you very much for your reply and apology for late.
Could you please help me to understand
GenerateSeries(1,16)
I see Value in new table end by 16 too.
All the best,
cocomy
- Ashish_MathurSuper User