Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Hi,
Here's another way. Download the file from here.
Hope this helps.
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 ) )
Please see attached file
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |