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

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

 

 

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

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
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 314 members 3,392 guests
Please welcome our newest community members: