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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Jos13
Helper III
Helper III

Current vs Previous Quarter Sales

Hi All,

 

I have the following tables

Table : Sales

Jos13_0-1638545033513.png

Table: Employee

Jos13_1-1638545057060.png

There exists a 1 to many relationship from Employee to Sales.

 

I want to display current vs previous quarter sales by employee in a table visual.

Jos13_2-1638545169046.png

When nothing is selected in Quarter slicer, CQ Sales should show sales for 2021 Q4. But it is not working. Measure PQ Sales should display the previous quarter sales for the corresponding employee.

I have written the measures as follows

Jos13_3-1638545406320.pngJos13_4-1638545433739.png

Please help me to fix this

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @Jos13 ,

 

To achieve this, you need to create a new seperate calculated table.

 

Quarter = DISTINCT('Sales'[Quarter])

 

vstephenmsft_0-1639536703069.png

The quarter table has no relationship with the main table.

vstephenmsft_1-1639536753458.png

In the sales table, I have expanded some data to increase the situation in different years.

vstephenmsft_2-1639537378953.png

Create two measures

 

CQ Sales = var _cq=SELECTEDVALUE('Quarter'[Quarter])
return CALCULATE(SUM('Sales'[Sales]),FILTER('Sales',[Quarter]=_cq))
PQ Sales = var _cq=SELECTEDVALUE('Quarter'[Quarter])
var _pq=IF(VALUE(RIGHT(_cq,1))=1,VALUE(LEFT(_cq,4))-1&" Q4",LEFT(_cq,4)&" Q"&VALUE(RIGHT(_cq,1))-1)
RETURN CALCULATE(SUM(Sales[Sales]),FILTER('Sales',[Quarter]=_pq))

 

Here's the results.

vstephenmsft_3-1639537451656.png

vstephenmsft_4-1639537520263.png

 

 

 

Best Regards,

Stephen Tao

 

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-stephen-msft
Community Support
Community Support

Hi @Jos13 ,

 

To achieve this, you need to create a new seperate calculated table.

 

Quarter = DISTINCT('Sales'[Quarter])

 

vstephenmsft_0-1639536703069.png

The quarter table has no relationship with the main table.

vstephenmsft_1-1639536753458.png

In the sales table, I have expanded some data to increase the situation in different years.

vstephenmsft_2-1639537378953.png

Create two measures

 

CQ Sales = var _cq=SELECTEDVALUE('Quarter'[Quarter])
return CALCULATE(SUM('Sales'[Sales]),FILTER('Sales',[Quarter]=_cq))
PQ Sales = var _cq=SELECTEDVALUE('Quarter'[Quarter])
var _pq=IF(VALUE(RIGHT(_cq,1))=1,VALUE(LEFT(_cq,4))-1&" Q4",LEFT(_cq,4)&" Q"&VALUE(RIGHT(_cq,1))-1)
RETURN CALCULATE(SUM(Sales[Sales]),FILTER('Sales',[Quarter]=_pq))

 

Here's the results.

vstephenmsft_3-1639537451656.png

vstephenmsft_4-1639537520263.png

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

ValtteriN
Super User
Super User

Hi,

You can use QUARTER and PREVIOUSQUARTER functions here.

My test data:

ValtteriN_1-1638787171378.png

 


Functions:

Current Quarter = CALCULATE(SUM('Quarter calculations'[Value]),QUARTER('Quarter calculations'[Date]))
Previous Quarter = CALCULATE(SUM('Quarter calculations'[Value]),PREVIOUSQUARTER('Calendar'[Date]))

 

Additional notes:
I had a one-to-many relationship  between my 'test data'[date] and 'calendar'[date].
IF you don't have a date column you can construct one from your 'Sales'[Quarter] by using this DAX:

Date from Quarter =
var _quarter = RIGHT('Quarter calculations'[Quarter (different format)],1) return
DATE(LEFT('Quarter calculations'[Quarter (different format)],4),

Switch(_quarter,"1",3,
"2",6,
"3",9,
"4",12),1)


Hopefully htis helps and if it does please accept this as a solution.
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.