How to Calculate Previous Year Sales with Date Filters Applied

[Lora_0121]

Hello.

I have slicers for Year, Quarter, and Team, and I am trying to calculate a metric that is affected by these filters.

1.  When all dates are selected, py_sales is displayed in table1, but not in table2.
 

2. When only one year is selected, py_sales is not displayed in either table1 or table2.

 

How can I calculate previous year sales for the selected period in the slicer?
Here is the py_sales formula I wrote:

PY_sales = 
CALCULATE(
    SUM(Sheet1[sales]), 
    SAMEPERIODLASTYEAR('Sheet1'[Date])
)

Thank you.

[v-sathmakuri]

Hi @Lora_0121 ,

I hope this information proves helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you.

[v-sathmakuri]

Hi @Lora_0121 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @Lora_0121 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian · Accepted solution]

Hi @Lora_0121 

A dedicated Dates dimension table with a full range of dates and columns for year, quarter, and month would simplify your time intelligence calculations. You can create such a table using DAX or M, relate its Date column to your fact table in a one-to-many relationship and ensure that the Dates table have been marked as such.

Please see the attached sample pbix.

[ryan_mayu]

@Lora_0121 

if you want to use time intelligence functions, you need to have a date table

https://learn.microsoft.com/en-us/dax/dateadd-function-dax?wt.mc_id=DP-MVP-5004616

https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax?wt.mc_id=DP-MVP-5004616

[Lora_0121]

'Sheet1'[Date] is in date format.

[ryan_mayu]

I know you have a date column in your main table.

You need to have a new date table and build relationship between two tables

pls see the attachment