cancel
Showing results for
Search instead for
Did you mean:
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 =
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 )
)```
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 =
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 )
)```
Super User

## Re: Produce monthly dataset by calculation

@cocomy

Please see attached file

Member

## Re: Produce monthly dataset by calculation

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

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

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.

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

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### 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:
Please welcome our newest community members: