Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kr1s
Frequent Visitor

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:

 

DatePayerCustomerProduct GroupProductQuantity
01/01/2016AA1PG1PG1A1
01/01/2016BB1PG2PG2A2
01/01/2016CC1PG1PG1A3
01/01/2016AA2PG1PG1A4
01/01/2016DD1PG2PG2A5
01/01/2017BB1PG2PG2A2
01/01/2017BB1PG1PG1A3
01/01/2017DD2PG2PG2A4
01/01/2017EE1PG1PG1A5
01/01/2017AA1PG1PG1A1
01/01/2018AA1PG1PG1A3
01/01/2018CC1PG1PG1A4
01/01/2018BB1PG2PG2A5
01/01/2018DD1PG2PG2A1
01/01/2018EE1PG1PG1A2

 

Here is what i expect to see from the output:

 

DatePayerCustomerProduct GroupProductQuantityLY Data
01/01/2016AA1PG1PG1A1 
01/01/2016BB1PG2PG2A2 
01/01/2016CC1PG1PG1A3 
01/01/2016AA2PG1PG1A4 
01/01/2016DD1PG2PG2A5 
01/01/2017BB1PG2PG2A22
01/01/2017BB1PG1PG1A3 
01/01/2017DD2PG2PG2A4 
01/01/2017EE1PG1PG1A5 
01/01/2017AA1PG1PG1A11
01/01/2018AA1PG1PG1A31
01/01/2018CC1PG1PG1A4 
01/01/2018BB1PG2PG2A54
01/01/2018DD1PG2PG2A1 
01/01/2018EE1PG1PG1A25

 

Any help is greatly appreciated.

 

Thanks,

 

Kristian

 

6 REPLIES 6
amitchandak
Super User
Super User

@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
Super User
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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Hi @Kr1s ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 

 

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

 

Regards,

 

Kristian

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

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors