DAX Year to Date in Previous/Prior Year

How Are We Doing THIS Year Versus the Same Time LAST Year?

This is a pretty common question, and a pretty common need.  But there’s no DAX function that just DOES this.

For instance, getting a“Year to Date” calculation for, say, Total Sales, is pretty straightforward:

[YTD Sales] = CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

Back to the point of this post:  it turns out that you can “nest” a DATESYTD inside of a DATEADD!

Try this:

[Prev Yr YTD Sales] = CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-1,Year))

We have two requirement:

  • You must have a well-constructed Calendar/Dates table
  • You need to then USE that Calendar/Dates table on your pivot table

if you want more info click this link

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...