Forum Discussion

Jos13's avatar
Jos13
Icon for Helper III rankHelper III
4 years ago
Solved

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

Please help me to fix this

All()
ALLEXCEPT
ALLSELECTED
DAX
Time Intelligence
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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]))

     

    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.
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.