cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cocomy Member
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

 

pbdataset.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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 )
)

View solution in original post

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

View solution in original post

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

View solution in original post

Super User
Super User

Re: Produce monthly dataset by calculation

@cocomy

 

Please see attached file

 

coco.png

cocomy Member
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

 

 

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

View solution in original post

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

Re: Produce monthly dataset by calculation

Hi,

 

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

 

Hope this helps.

cocomy Member
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
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 902 guests
Please welcome our newest community members: