Produce monthly dataset by calculation

[cocomy]

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

[Zubair_Muhammad · Accepted solution]

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

[Ashish_Mathur]

Hi,

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

Hope this helps.

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad]

@cocomy

Please see attached file

[cocomy]

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

[Zubair_Muhammad · Accepted solution]

@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]

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

[cocomy]

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

All thebes
cocomy