cancel
Showing results for
Did you mean:
Highlighted
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

@cocomy

Member

## Re: Produce monthly dataset by calculation

Hi Zubair,

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
Super User

Hi,

Hope this helps.

Member

## Re: Produce monthly dataset by calculation

Hi Zubair,

Thank you for helping me out on this.

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

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 314 members 3,392 guests
Recent signins: