DAX - Calculate same period of previous year

[Diego_Vialle]

I have the following formula below in DAX, to calculate customers who NEVER PURCHASED BUT PURCHASED in the last two months. How do I use SAMEPERIODLASTYEAR for her?

Novos Clientes Bimestrais =

VAR AllOldCustomers =

CALCULATETABLE (

VALUES ( SBOPRODMS[Nome do PN] ),

SBOPRODMS,

SBOPRODMS[Data NF]

< TODAY () - 60,

SBOPRODMS[Documento] = "Nota fiscal de saída"

)

VAR AllNewCustomers =

CALCULATETABLE (

VALUES ( SBOPRODMS[Nome do PN] ),

SBOPRODMS[Data NF]

< TODAY () ,

SBOPRODMS[Documento] = "Nota fiscal de saída"

)

RETURN

IF(ISBLANK((COUNTROWS ( EXCEPT (AllNewCustomers, AllOldCustomers )))),0,COUNTROWS ( EXCEPT (AllNewCustomers, AllOldCustomers )))

[tamerj1 · Accepted solution]

@Diego_Vialle 

Please try

Novos Clientes Bimestrais PY =
VAR PreviousYearDate =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Nome do PN] ),
        SBOPRODMS,
        SBOPRODMS[Data NF] < PreviousYearDate - 60,
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Nome do PN] ),
        SBOPRODMS[Data NF] < PreviousYearDate,
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    )
RETURN
    IF (
        ISBLANK ( ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) ) ),
        0,
        COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )
    )

[Whitewater100]

Hello:

Once you have you measure for this year, I'll name it Measure A then 

SPLY = CALCULATE([Measure A], SAMEPERIODLASTYEAR([Dates, Date]))

Dates[Date] is the date field from your date table. It is critical to have date table, marked as date table and connected to your fact table with one to many relationship. Then the time intel functions work as expected.

I hope this helps!

[Diego_Vialle]

I used it as directed and got the same result as the current period:

Novos Clientes Bimestrais LY = CALCULATE([Novos Clientes Bimestrais],SAMEPERIODLASTYEAR(dCalendario[Date].[Date]))

[Whitewater100]

Hi Diego:

I will attah a file. Please look at inventory measure and Inv SPLY. This should help explain. The Date Table plays a big role in this.

https://drive.google.com/file/d/14G9U23xIV9_f57l2M7ChY-KWPif8M3Dp/view?usp=sharing 

Inv Total = SUM(Data[Invoice Amount])

Inv Total SPLY = CALCULATE([Inv Total], SAMEPERIODLASTYEAR(Dates[Date]))

I hope this helps!

[tamerj1]

Hi Diego

try delete ".[Date]" part. If not working try by adding REMOVEFILTERS ( dCalendario )