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)   ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
cocomy
Resolver I
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    cocomy
    Resolver I
    8 years ago
    Thank you so much! I could not figure that is the number if months.

    All thebes
    cocomy
  • cocomy's avatar
    cocomy
    Resolver I
    8 years ago

    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