Forum Discussion

cocomy's avatar
cocomy
Resolver I
8 years ago
Solved

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

 

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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion

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