cancel
Showing results for
Did you mean:

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

Helper III

## Current vs Previous Quarter Sales

Hi All,

I have the following tables

Table : Sales

Table: Employee

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.

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

1 ACCEPTED SOLUTION
Community Support

Hi @Jos13 ,

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

Quarter = DISTINCT('Sales'[Quarter])

The quarter table has no relationship with the main table.

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

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.

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.

2 REPLIES 2
Community Support

Hi @Jos13 ,

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

Quarter = DISTINCT('Sales'[Quarter])

The quarter table has no relationship with the main table.

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

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.

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.

Super User

Hi,

You can use QUARTER and PREVIOUSQUARTER functions here.

My test data:

Functions:

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

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.

Proud to be a Super User!