topic DAX - Calculate same period of previous year in DAX Commands and Tips

DAX - Calculate same period of previous year

Diego_Vialle — Mon, 16 May 2022 15:18:54 GMT

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

Re: DAX - Calculate same period of previous year

Whitewater100 — Mon, 16 May 2022 15:30:59 GMT

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!

Re: DAX - Calculate same period of previous year

Diego_Vialle — Mon, 16 May 2022 16:38:03 GMT

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

Re: DAX - Calculate same period of previous year

tamerj1 — Mon, 16 May 2022 17:24:01 GMT

Hi Diego

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

Re: DAX - Calculate same period of previous year

Whitewater100 — Mon, 16 May 2022 17:50:42 GMT

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!

Re: DAX - Calculate same period of previous year

tamerj1 — Mon, 16 May 2022 19:02:28 GMT

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