Forum Discussion

LeaRupnik's avatar
LeaRupnik
Helper III
6 years ago
Solved

sum column

Hello,  a have a problem, how to sum the sum column.  i have a exemple, a wont a sum column sum witch is sum jan in feb  and a get 1+2 = 3 then 3+3 = 6 then 6+4 = 10,....   date number sum ...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi LeaRupnik ,

    Try this:

    1. Create a Calendar table.

    Calendar = CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) )

    2. Create columns in Calendar table.

    Month = MONTH('Calendar'[Date])
    Month Name = FORMAT('Calendar'[Date],"mmm")

    3. Create relationship.

    4. Create measures.

    MTD = TOTALMTD(SUM('Table'[quantiti]),'Calendar'[Date])
    YTD =
IF (
    NOT ( ISBLANK ( MAX ( 'Table'[quantiti] ) ) ),
    TOTALYTD ( SUM ( 'Table'[quantiti] ), 'Calendar'[Date] )
)

    PBIX file attached.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Because there is no logical way to sort just the names of each month, this isn't possible. But add a date or the monthnumber (1-12) to your table and it is possible.

 

If you have the possibility to add a column with the number of each month or a column with a date you can use:

 

 
 
CalculatedTable = CALCULATE(SUM('Table'[values]);FILTER('Table';'Table'[monthnr]<=EARLIER('Table'[monthnr])))
  • LeaRupnik's avatar
    LeaRupnik
    Helper III
    6 years ago

    Hello, 

     

    this is work, but i have a table more complicated. 

    I have quantity according to date, then i have to sum a quantity to month, then to each month together. 

    ia have that kind of problem

    datequantiti 
    1.02.20192 
    12.02.20195 
    14.02.20195 
    3.03.20194 
    5.03.20196 
    5.04.20192 
    6.04.20195

     

     

    and i would like to get this

    feb1212
    mar1022
    apr729
     29 

     

    thx Lea

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi LeaRupnik ,

      Try this:

      1. Create a Calendar table.

      Calendar = CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) )

      2. Create columns in Calendar table.

      Month = MONTH('Calendar'[Date])
      Month Name = FORMAT('Calendar'[Date],"mmm")

      3. Create relationship.

      4. Create measures.

      MTD = TOTALMTD(SUM('Table'[quantiti]),'Calendar'[Date])
      YTD =
IF (
    NOT ( ISBLANK ( MAX ( 'Table'[quantiti] ) ) ),
    TOTALYTD ( SUM ( 'Table'[quantiti] ), 'Calendar'[Date] )
)

      PBIX file attached.

       

      Best Regards,

      Icey

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • LeaRupnik's avatar
        LeaRupnik
        Helper III
        6 years ago

        i a new in this, in Power bi and the forum. 

        The solution witch post it work. Now a have a problem how to sort the short month name. I don't want the number month. 

        tnx

        Lea