Same period last year

[zibster]

Hi Can anyone tell me how to fix my DAX, when I filter year my last year is not populating,

LY = CALCULATE(SUM(Sales[Sales]),

FILTER(

ALL(Sales),

Sales[txn_dte]=SAMEPERIODLASTYEAR(Sales[txn_dte].[Date])

)

)

Thanks

Z

[v-easonf-msft]

Hi , @zibster 

If your dates are not continuous, it is recommended to create a date table.

Date = CALENDARAUTO()

And establish relationships between tables

Then you can try measure as below:

LY =
SUMX (
    Sales,
    CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

The result will show as below.

Here is a demo

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

[zibster]

Hi Thanks for all the excamples but i still have the same problem that LY data is not populating when the year is selected, for excample i have 4 years of date 2017, 2018, 2019, 2020. When i sellect 2019 and 2018 I see LY sales only for 2019 and 2018 shows nothing, I was expecting to see LY sales for 2018 as well since i do have the data.

[v-easonf-msft]

Hi , @zibster 

Everything works well on my side. If I don’t know your data model, it is difficult to judge the specific reason.

Can you show me your table relationship view ?

Where does the “year” field using in the slicer come from, year in a  date hierarchy  or  year created by yourself?

In addition ,please check again whether  there is a problem with your  data of "date" field.

Best Regards,
Community Support Team _ Eason

[zibster]

Hi,

The year is comming from date hierarchy, below is a sample of the data.

Thanks Z

[zibster]

Also,

Below is a link to the source file.

Sales File Link 

Thanks for all help

[Ashish_Mathur]

Hi,

There is a problem in downloading the file from OneDrive.  Upload it to Google Drive and share the download link.

[zibster]

Hi, Here is a link to GoogleDrive 

GoogleDrive 

Thanks

Z

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[zibster]

Hi Ashish.

Thanks for your help on this 🙂 it works perfect.

Thanks

Z

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @Ashish_Mathur I am trying to get the same results, but still not displaying Sameperiodlast Year Value. Have tried your soluction, but didn't work on my end. Please Help! 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi Thanks a lot for your Response. Initially I was working in a single table, but isoleted the dates into a calendar table as per your example and still didn't work. wen I filter for a specific Year it seems to return a total, but all the rows are empty. This is what I did. 

1- created a Calendar Table : 

Calendar = calendar (MIN('Invoice Perspective'[Dim Invoice Transaction Date.Date Name Numeric]),MAX('Invoice Perspective'[Dim Invoice Transaction Date.Date Name Numeric]))

2- Set the calendar table as a date table

3- linked the Calendar table to the Invoice perspective table.

4 - calculated total sales:

Total Sales = SUM('Invoice Perspective'[Billing Category Sales Dollars])

Calculated Sales for the previous Year with 3 different Dax, but none is working:

SalesLY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

Measure 2 = CALCULATE(SUM('Invoice Perspective'[Billing Category Sales Dollars]),DATEADD('Calendar'[Date],-1,YEAR))

Previous Year = CALCULATE(SUM('Invoice Perspective'[Billing Category Sales Dollars]), PREVIOUSYEAR('Invoice Perspective'[Dim Invoice Transaction Date.Date Name Numeric]))

Please see attached how I am trying to filter on my test page and the data model .

 

[Ashish_Mathur]

Hi,

That information will not help me.  share a small dummy dataset, explain the business context and clearly show the result that you are expecing so that i can frame a working solution.

[Anonymous]

Hi @Ashish_Mathur, the business scenario is that when  a date range is selected from current period (eg: 1/1/2020 - 1/31/2020) all of the transations ( Every row in the date range) are selected and individual sales for both sales from (1/1/2020 - 1/31/2020) and  ( 1/1/2019 - 1/31/2019) are  displayed including  the Diff in %. 

[Ashish_Mathur]

Hi,

As i have requested, share a dummy dataset for me to work on.

[Anonymous]

Hi @Ashish_Mathur , I thnk this data set would translate my problem.  I need to compare total sales in April 2019 to total sales in April 2018. 

https://drive.google.com/file/d/1n9wlwvKn2I65uTbtcpO_9ybEOpwfkKW_/view?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi Ashish, 

Thanks for your feedback, but I found another solution. 

Thanks, 

[Anonymous]

Hi Ashish, 

I have a Calendar table related to two other tables in a (1:*) relationship, but I am not able to correctly display data from both tables  under the same visual. It only dsplays correctlybetween dates from the calendar table and one of the tables at a time. Otherwise I have a blank visual  

This is the DAX I am using to create the calendar :

Calendar = calendar (MIN('Invoice'[Dim Invoice Transaction Date.Date Name Numeric])

,MAX('Invoice'[Dim Invoice Transaction Date.Date Name Numeric]))

This is how the tables are related :