SAMEPERIODLASTYEAR with filters

[Kr1s]

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

[amitchandak]

@Kr1s 

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/

[Pragati11]

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

[Kr1s]

Hi @Pragati11,

I saw that but it shows only 1 filter whereas i need 4. Also I need the calculation to read what the product is as we have over 200 so I can't hard code it all into the filter.

Thanks,

Kristian

[Pragati11]

Hi @Kr1s ,

Is it possible for you to share sample data or .pbix file?

Thanks,

Pragati

[Kr1s]

Hi @Pragati11 

Unfortunately I can't due to the sensitivity of the data.

Regards,

Kristian

[Pragati11]

Hi @Kr1s ,

In the following link I see a way to use SAMEPERIODLASTYEAR dax function with multiple filters. 

https://community.powerbi.com/t5/Desktop/DAX-SAMEPERIODLASTYEAR-Filter/td-p/92084

In the above link you see a dax calculation using 2 filters with && operator:

Measure =
    CALCULATE (
        DISTINCTCOUNT ( Sales[DocumentNumber] );
        FILTER ( ALL ( Sales ); Sales[Field1] = "1" && Sales[Field2] = VALUE ( 1 ) );
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )

 You can try something similar to add more filters here.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati