Forum Discussion
Compare Current Quarter + Week Number Vs Previous Quarter + Week Number
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
- Current Quarter = 2019 Q3
- Previous Quarter = 2019 Q2
- This Quarter in Previous Year = 2018 Q3
- 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 |
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.
2 Replies
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,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.
