Cumulative total by quarters

[Anonymous]

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.

Year	Quarter	Week	Sales	Cumulative Sales
2018	Q1	1	$35	$35
2018	Q1	2	$38	$73
2018	Q1	3	$63	$136
2018	Q1	4	$98	$234
2018	Q1	5	$40	$274
2018	Q1	6	$62	$336
2018	Q1	7	$96	$432
2018	Q1	8	$95	$527
2018	Q1	9	$77	$604
2018	Q1	10	$28	$632
2018	Q1	11	$89	$721
2018	Q1	12	$11	$732
2018	Q1	13	$99	$831
2018	Q2	14	$15	$15
2018	Q2	15	$52	$67
2018	Q2	16	$55	$122
2018	Q2	17	$8	$130
2018	Q2	18	$17	$147
2018	Q2	19	$66	$213
2018	Q2	20	$14	$227
2018	Q2	21	$2	$229
2018	Q2	22	$75	$304
2018	Q2	23	$75	$379
2018	Q2	24	$39	$418
2018	Q2	25	$8	$426
2018	Q2	26	$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.

[v-cherch-msft · Accepted solution]

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]

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.

[Vvelarde]

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

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 Quarter	Week Number	Total Price.amount
4	1	$40,000
4	2	$77,500
4	3	$237,170
4	5	$15,000
1	14	$60,000
1	15	$150,000
1	17	$130,000
1	18	$33,000
1	19	$75,000
1	20	$146,000
1	21	$120,000
1	22	$443,000
1	24	$154,500
1	25	$145,000
1	26	$292,500
2	27	$20,000
2	28	$10,000
2	29	$120,000
2	30	$235,500
2	31	$396,500
2	32	$12,500
2	34	-$25,000
2	35	$110,000
2	36	$40,000
2	37	$210,000
2	38	$360,000
2	39	$64,000
2	40	$101,000
3	40	-$20,000
3	41	$27,500
3	42	$70,000
3	43	$211,000
3	44	$451,000
3	45	$93,169
3	46	$33,000
3	48	$460,500
3	49	$50,000
3	50	$25,000
3	51	$188,000
3	52	$10,000
3	53	$65,000