Forum Discussion

powerbirino's avatar
powerbirino
Frequent Visitor
9 years ago
Solved

DAX SAMEPERIODLASTYEAR + Filter?

Currently I'm using this DAX formula on a Card and it's working:

 

CALCULATE(
	DISTINCTCOUNT(Sales[DocumentNumber]);
	FILTER(Sales; Sales[Field1] = "1" && Sales[Field2] = VALUE(1))
)

But, I want to combine it with the function SAMEPERIODLASTYEAR, where I've tried:

 

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

 

And the result doesn't change, when it acutually has to change, so, how can I do this?

 

Thanks!

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    powerbirino

     

    Please update the formula as below and try again.

     

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

     

    Best Regards,

    Herbert

7 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    powerbirino

     

    Please try to use following DAX formula.

     

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

     

    Best Regards,

    Herbert

    • powerbirino's avatar
      powerbirino
      Frequent Visitor
      9 years ago

      v-haibl-msft

       

      Trying that gives (Blank), which is incorrect because it should give a digit.

       

      I know it's wrong because filtering by 2014 the result in the "Current year" Card visual (for instance) gives 3000, and the same visual filtering by 2013 gives 800, but, using the "Last year" Card visual and filtering by 2014 with your solution, gives (Blank), when it should give 800.

       

      I have tried other solutions but I can't achieve this dumb thing :/

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        powerbirino

         

        Please update the formula as below and try again.

         

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

         

        Best Regards,

        Herbert

  • houdafrigui's avatar
    houdafrigui
    Regular Visitor
    6 years ago

    Hi, please , i was trying a formular for:  calculate , in sameperiodlastyear but with removing the day 29 of April (2019 not exsiting),

    whihc one i have to use?

    • houdafrigui's avatar
      houdafrigui
      Regular Visitor
      6 years ago
      LastYearAmount = CALCULATE(BaseAmount,SAMEPERIODLASTYEAR('calendartable'[Date])) This is my formular that imusing for the moment
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Can i apply SAMEPERIODLASTYEAR on a measure? How?