Forum Discussion
SAMEPERIODLASTYEAR with filters
Hi,
I have a dataset consisting of all sales from 2016 to present day. There are multiple entries per date broken down by payer, customer, product group and product.
Whenever I use SAMEPERIODLASTYEAR it returns nothing. I am assuming due to the complexity of the data.
Is there a way to use SAMEPERIODLASTYEAR with 4 filters, i.e. if payer, customer, product group and product match?
I have tried doing the filter myself but still can't get any meaning full data.
Here is an example of my dataset:
| Date | Payer | Customer | Product Group | Product | Quantity |
| 01/01/2016 | A | A1 | PG1 | PG1A | 1 |
| 01/01/2016 | B | B1 | PG2 | PG2A | 2 |
| 01/01/2016 | C | C1 | PG1 | PG1A | 3 |
| 01/01/2016 | A | A2 | PG1 | PG1A | 4 |
| 01/01/2016 | D | D1 | PG2 | PG2A | 5 |
| 01/01/2017 | B | B1 | PG2 | PG2A | 2 |
| 01/01/2017 | B | B1 | PG1 | PG1A | 3 |
| 01/01/2017 | D | D2 | PG2 | PG2A | 4 |
| 01/01/2017 | E | E1 | PG1 | PG1A | 5 |
| 01/01/2017 | A | A1 | PG1 | PG1A | 1 |
| 01/01/2018 | A | A1 | PG1 | PG1A | 3 |
| 01/01/2018 | C | C1 | PG1 | PG1A | 4 |
| 01/01/2018 | B | B1 | PG2 | PG2A | 5 |
| 01/01/2018 | D | D1 | PG2 | PG2A | 1 |
| 01/01/2018 | E | E1 | PG1 | PG1A | 2 |
Here is what i expect to see from the output:
| Date | Payer | Customer | Product Group | Product | Quantity | LY Data |
| 01/01/2016 | A | A1 | PG1 | PG1A | 1 | |
| 01/01/2016 | B | B1 | PG2 | PG2A | 2 | |
| 01/01/2016 | C | C1 | PG1 | PG1A | 3 | |
| 01/01/2016 | A | A2 | PG1 | PG1A | 4 | |
| 01/01/2016 | D | D1 | PG2 | PG2A | 5 | |
| 01/01/2017 | B | B1 | PG2 | PG2A | 2 | 2 |
| 01/01/2017 | B | B1 | PG1 | PG1A | 3 | |
| 01/01/2017 | D | D2 | PG2 | PG2A | 4 | |
| 01/01/2017 | E | E1 | PG1 | PG1A | 5 | |
| 01/01/2017 | A | A1 | PG1 | PG1A | 1 | 1 |
| 01/01/2018 | A | A1 | PG1 | PG1A | 3 | 1 |
| 01/01/2018 | C | C1 | PG1 | PG1A | 4 | |
| 01/01/2018 | B | B1 | PG2 | PG2A | 5 | 4 |
| 01/01/2018 | D | D1 | PG2 | PG2A | 1 | |
| 01/01/2018 | E | E1 | PG1 | PG1A | 2 | 5 |
Any help is greatly appreciated.
Thanks,
Kristian
6 Replies
- Pragati11
Super User
Hi Kr1s ,
There is already an existing thread for this:
https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-filter/m-p/604118
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
- amitchandak
SAMEPERIODLASTYEAR and all time intelligence function work better with a date calendar, Hope you are using one.
You can also use a year behind measure like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
