Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi All,

 

How to compare Current Quarter + Week Number Vs Previous Quarter + Week Number VCurrent 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

Screenshot_5.png

 

Account IdOpportunity IdAmountClose DateUser Name
A1Opp1$150,0002/1/2018Sean Barton
A1Opp2$40,0005/28/2018Sean Barton
A1Opp3$10,0008/21/2018Rachel Stark
A1Opp4$20,00010/18/2018Sean Barton
A1Opp5$15,00011/7/2018Sean Barton
A1Opp6$75,0003/26/2019Sean Barton
A1Opp7$85,0005/30/2019Sean Barton
A1Opp8$150,0006/15/2019Rachel Stark
A1Opp9$15,0009/5/2019Rachel Stark
A1Opp10$50,00010/16/2019Sean Barton
A2Opp11$120,0003/1/2018Sean Barton
A2Opp12$5,0006/3/2018Rachel Stark
A2Opp13$10,0009/20/2018Sean Barton
A2Opp14$5,00011/24/2018Rachel Stark
A2Opp15$50,00012/31/2018Sean Barton
A2Opp16$120,0002/14/2019Sean Barton
A2Opp17$75,0005/18/2019Sean Barton
A2Opp18$75,0005/29/2019Rachel Stark
A2Opp19$85,0007/31/2019Sean Barton
A2Opp20$85,00010/31/2019Sean Barton
A2Opp21$5,00011/28/2019Sean Barton
A3Opp22$50,0002/22/2018Rachel Stark
A3Opp23$100,0004/28/2018Sean Barton
A3Opp24$40,0008/1/2018Rachel Stark
A3Opp25$35,0008/31/2018Sean Barton
A3Opp26$5,00011/14/2018Sean Barton
A3Opp27$10,00010/29/2018Rachel Stark
A3Opp28$85,0002/5/2019Rachel Stark
A3Opp29$40,0006/3/2019Sean Barton
A3Opp30$75,0007/26/2019Rachel Stark
A3Opp31$5,00012/21/2019Sean Barton
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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.

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.

View solution in original post

2 REPLIES 2
v-lid-msft
Community Support
Community Support

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.

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
Advocate II
Advocate II

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.