Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative total by quarters

Hello,

 

I have found a lot of solutions for Cumulative Sums. However, I have not been able to find a solution that sums Sales by week for a quarter. Then, at the beginning of the next quarter, resets and starts a new cumulative sum for the weeks in that quarter, and so on.

 

Here is sample data I am working with and an example of what the chart should look like.

 

YearQuarterWeekSalesCumulative Sales
2018Q11$35$35
2018Q12$38$73
2018Q13$63$136
2018Q14$98$234
2018Q15$40$274
2018Q16$62$336
2018Q17$96$432
2018Q18$95$527
2018Q19$77$604
2018Q110$28$632
2018Q111$89$721
2018Q112$11$732
2018Q113$99$831
2018Q214$15$15
2018Q215$52$67
2018Q216$55$122
2018Q217$8$130
2018Q218$17$147
2018Q219$66$213
2018Q220$14$227
2018Q221$2$229
2018Q222$75$304
2018Q223$75$379
2018Q224$39$418
2018Q225$8$426
2018Q226$22$448

 

 

NOTE: I have many opportunities that close each week that would need to be summed for each week. The sample data above does not reflect this. I merely used the sample data to illustrate the end result I am looking to achieve. 

 

I will gladly offer more information to anyone able to assist or direct me to a solution.

 

Thank you.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    The above formula is a measure.I would provide the formula of calculated column.Attached the sample file for your reference.

    Column =
SUMX (
    FILTER (
        Table1,
        Table1[Fiscal Quarter] = EARLIER ( Table1[Fiscal Quarter] )
            && Table1[Week Number] <= EARLIER ( Table1[Week Number] )
    ),
    Table1[Total Price.amount]
)

    Regards,

    Cherie

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    7 years ago

    Anonymous

     

    Hi, try with this,

     

    CumulativeSales =
VAR _Quarter =
    SELECTEDVALUE ( Table1[Quarter] )
VAR _Week =
    SELECTEDVALUE ( Table1[Week] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER ( ALL ( Table1 ), Table1[Quarter] = _Quarter && Table1[Week] <= _Week )
    )

    Regards

     

    Victor

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Victor. Many thanks for your reply. Unfortunately, it did not work. The formula was accepted but no results were returned. Here is the actual table data (below0 with the week number, fiscal quarter and sales amount by week. Our fiscal year runs April - March. Weeks 1 - 13 are fiscal quarter 4.

       

      I am hoping a solution can be found.

       

      Thank you.

       

      Fiscal QuarterWeek NumberTotal Price.amount
      41$40,000
      42$77,500
      43$237,170
      45$15,000
      114$60,000
      115$150,000
      117$130,000
      118$33,000
      119$75,000
      120$146,000
      121$120,000
      122$443,000
      124$154,500
      125$145,000
      126$292,500
      227$20,000
      228$10,000
      229$120,000
      230$235,500
      231$396,500
      232$12,500
      234-$25,000
      235$110,000
      236$40,000
      237$210,000
      238$360,000
      239$64,000
      240$101,000
      340-$20,000
      341$27,500
      342$70,000
      343$211,000
      344$451,000
      345$93,169
      346$33,000
      348$460,500
      349$50,000
      350$25,000
      351$188,000
      352$10,000
      353$65,000
  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    The above formula is a measure.I would provide the formula of calculated column.Attached the sample file for your reference.

    Column =
SUMX (
    FILTER (
        Table1,
        Table1[Fiscal Quarter] = EARLIER ( Table1[Fiscal Quarter] )
            && Table1[Week Number] <= EARLIER ( Table1[Week Number] )
    ),
    Table1[Total Price.amount]
)

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Cherie,

       

      THANK YOU!!! This worked perfectly for me. I can't thank you enough!! I feel as if my Power BI acumen skyrocketed. You time and effort is greatly appreciated.