Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 |
Solved! Go to 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.
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.
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.:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |