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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DavidM06
Regular Visitor

Filters DAX sameperiodlast year issue

morning all

believe it or not, i did not find the solution here...:)

i have a Clustered Column chart with values forlast 6 months , based on "SignatureDate". to get these values, I use a filter on SignatureDate for the last 6 months.

I created a measurement to get the same numbers last years

LY = CALCULATE(SUM(Haraka[TCV]),SAMEPERIODLASTYEAR(CRM[DateSignature].[Date]))
 Goal : compare the number of this years with the numbers of last year at the same period
The problem is that the LY measureemtn are also impacted by the filter, and are not displayed cause the SignatureDate is more than 6 months !!!
Any idea to filter my number AND NOT  the LY ones ?
thnaks
DAvidM
3 REPLIES 3
Tahreem24
Super User
Super User

@DavidM06 Here are the two suggestions for your problem.

Suggestion 1: Remove .[Date] from SAMEPERDIODLASTYEAR and keep like this 

LY = CALCULATE(SUM(Haraka[TCV]),SAMEPERIODLASTYEAR(CRM[DateSignature]))
 
Suggestion 2: Create a separate calender table and use this in SAMEPERIODLASTYEAR.
Calendar Table = CALENDAR(MIN(DateSignature),MAX(DateSignature))
Then connect this Date to CRM table's DateSignature using 1-many relationship and then create a below measure:
LY = CALCULATE(SUM(Haraka[TCV]),SAMEPERIODLASTYEAR(Calendar[Date]))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
v-stephen-msft
Community Support
Community Support

Hi @DavidM06 ,

 

You can use the ALLEXCEPT function in your LY measure to ignore the filter on SignatureDate. Here's an updated version of your LY measure:

LY = CALCULATE(
    SUM(Haraka[TCV]),
    SAMEPERIODLASTYEAR(CRM[DateSignature].[Date]),
    ALLEXCEPT(Haraka, Haraka[SignatureDate])
)

 

This measure will calculate the sum of TCV for the same period last year, but it will not be affected by the filter on SignatureDate.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Hi

Stephen solution seems to be the solution, but i get some errors

i use the following formula

TCV LY = CALCULATE(
    SUM(CRM[TCV]),
    SAMEPERIODLASTYEAR(CRM[DateSignature].[Date]),
    ALLEXCEPT(CRM,CRM[DateSignature])
)
 
and do not get the correct  number ( i manully calculated the value i should get)
also, when I change the filter on the relative date like 6 months, 8 months, TCV does not change and TCV LY apprears  only when i select 7 months period

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.