Filters DAX sameperiodlast year issue

[DavidM06]

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

[Tahreem24]

@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]))

[v-stephen-msft]

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.           

[DavidM06]

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