Forum Discussion

DavidM06's avatar
DavidM06
Regular Visitor
3 years ago

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
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      DavidM06
      Regular Visitor
      3 years ago

      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
  • Tahreem24's avatar
    Tahreem24
    Super User
    3 years ago

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