Compare Current Quarter + Week Number Vs Previous Quarter + Week Number

[Anonymous]

Hi All,

How to compare Current Quarter + Week Number Vs Previous Quarter + Week Number Vs Current Quarter + Week Number 

Ex: I want to compare 2018 Q3 Week2 with 2018 Q2 Week2 & 2017 Q3 Week2

output Expectation:  want to show the cumulative sum by Week & Quarter (Current Q vs Previous Q vs Current Q in Last Year)

I have selected 2019 Q3 in the Quarter selection filter so I want to show the following quarter in Cumulative sales Trend 

1. Current Quarter =  2019 Q3
2. Previous Quarter = 2019 Q2
3. This Quarter in Previous Year = 2018 Q3
4. Other Quarte = 2019 Q1, 2018 Q4

Account Id	Opportunity Id	Amount	Close Date	User Name
A1	Opp1	$150,000	2/1/2018	Sean Barton
A1	Opp2	$40,000	5/28/2018	Sean Barton
A1	Opp3	$10,000	8/21/2018	Rachel Stark
A1	Opp4	$20,000	10/18/2018	Sean Barton
A1	Opp5	$15,000	11/7/2018	Sean Barton
A1	Opp6	$75,000	3/26/2019	Sean Barton
A1	Opp7	$85,000	5/30/2019	Sean Barton
A1	Opp8	$150,000	6/15/2019	Rachel Stark
A1	Opp9	$15,000	9/5/2019	Rachel Stark
A1	Opp10	$50,000	10/16/2019	Sean Barton
A2	Opp11	$120,000	3/1/2018	Sean Barton
A2	Opp12	$5,000	6/3/2018	Rachel Stark
A2	Opp13	$10,000	9/20/2018	Sean Barton
A2	Opp14	$5,000	11/24/2018	Rachel Stark
A2	Opp15	$50,000	12/31/2018	Sean Barton
A2	Opp16	$120,000	2/14/2019	Sean Barton
A2	Opp17	$75,000	5/18/2019	Sean Barton
A2	Opp18	$75,000	5/29/2019	Rachel Stark
A2	Opp19	$85,000	7/31/2019	Sean Barton
A2	Opp20	$85,000	10/31/2019	Sean Barton
A2	Opp21	$5,000	11/28/2019	Sean Barton
A3	Opp22	$50,000	2/22/2018	Rachel Stark
A3	Opp23	$100,000	4/28/2018	Sean Barton
A3	Opp24	$40,000	8/1/2018	Rachel Stark
A3	Opp25	$35,000	8/31/2018	Sean Barton
A3	Opp26	$5,000	11/14/2018	Sean Barton
A3	Opp27	$10,000	10/29/2018	Rachel Stark
A3	Opp28	$85,000	2/5/2019	Rachel Stark
A3	Opp29	$40,000	6/3/2019	Sean Barton
A3	Opp30	$75,000	7/26/2019	Rachel Stark
A3	Opp31	$5,000	12/21/2019	Sean Barton

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the similar thread, we have post the formula and sample file there: https://community.powerbi.com/t5/Desktop/Rolling-5-quarter-restriction-based-on-Year-Quarter-Selection/td-p/823259

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leed]

Hi @Anonymous 

I had a similar issue in the past, I think M is the best way to resolve it.

I will divide it in 2 parts:

-In M, you use a formula to determine if the date is in current quarter, past quarter etc.: 

#"Added Custom - Quarter Slicer" = Table.AddColumn(#"XXX", "Quarter Slicer", each if Date.IsInCurrentQuarter([Date]) then "Current Quarter" else if Date.IsInPreviousQuarter([Date]) then "Last Quarter" else [Calendar Quarter Name])

-In DAX : Using CALCULATE() and add the filter on the column like:

CALCULATE ( measures, [Quarter Slicer] = "Current Quarter" / "Last Quarter" etc.)

Best,