cancel
Showing results for
Did you mean:
cocomy Member

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 2 ACCEPTED SOLUTIONS

Accepted Solutions Super User

Re: Produce monthly dataset by calculation

@cocomy

You can create a Calculated Table

From the Modelling Tab>>New Table

New Table =
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 )
) Super User

Re: Produce monthly dataset by calculation

@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

7 REPLIES 7 Super User

Re: Produce monthly dataset by calculation

@cocomy

You can create a Calculated Table

From the Modelling Tab>>New Table

New Table =
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 )
) Super User

Re: Produce monthly dataset by calculation

@cocomy

Please see attached file cocomy Member

Re: Produce monthly dataset by calculation

Hi Zubair,

Thank you very much for your reply and apology for late.

GenerateSeries(1,16)

I see Value in new table end by 16 too.

All the best,

cocomy Super User

Re: Produce monthly dataset by calculation

@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

cocomy Member

Re: Produce monthly dataset by calculation

Thank you so much! I could not figure that is the number if months.

All thebes
cocomy
Highlighted Super User

Re: Produce monthly dataset by calculation

Hi,

Here's another way.  Download the file from here.

Hope this helps.

cocomy Member

Re: Produce monthly dataset by calculation

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 =
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

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 474 members 3,532 guests
Recent signins:
• Paulrms • sbhan4 • JeanPierre18 • jpl • marci640 • webportal • maxtank • AMaxamP3 • pdc1988 • RoopaV • apache78 • CSG_ • gauthamboppana • Ankitkmr122 