topic Re: Between lastyear and same date of lastdate of this year in DAX Commands and Tips

Between lastyear and same date of lastdate of this year

Lexarino — Thu, 28 Apr 2022 13:06:29 GMT

Hello !

Try to compare data range from begining of last year and the day of last data update, but replace this day to last year.
But don't know where and how specify last year paramater in my DAX command :

DATESBETWEEN(Calendar[Date], DATE(YEAR(TODAY()-1,1,1), LASTDATE(EcoVisio[Date]) - 1 year !

LASTDATE(EcoVisio[Date]) as this format "2022-04-28" and would like "2021-04-28".

Thanks you for your help !

Re: Between lastyear and same date of lastdate of this year

johnt75 — Thu, 28 Apr 2022 13:15:28 GMT

var endDate = LASTDATE(EcoVisio[Date]) return DATESBETWEEN( 'Calendar'[Date], DATE( YEAR(TODAY())-1, 1, 1), DATE( YEAR(endDate) - 1, MONTH(endDate), DAY(endDate)))

Re: Between lastyear and same date of lastdate of this year

Lexarino — Thu, 28 Apr 2022 13:31:31 GMT

Seems to be the perfect solution but don't find the correct syntax to integrate it 😕

CALCULATE(SUM(EcoVisio[BIT Arzon]) + SUM(EcoVisio[BIT Vannes]),

var endDate = LASTDATE(EcoVisio[Date])

return

DATESBETWEEN(Ecovisio[Date], DATE(YEAR(TODAY())-1, 1, 1), DATE(YEAR('endDate') - 1, MONTH('endDate'), DAY('endDate')))

)

I'm such a looser in DAX !!

Re: Between lastyear and same date of lastdate of this year

johnt75 — Thu, 28 Apr 2022 13:34:41 GMT

My Measure = VAR endDate = LASTDATE ( EcoVisio[Date] ) RETURN CALCULATE ( SUM ( EcoVisio[BIT Arzon] ) + SUM ( EcoVisio[BIT Vannes] ), DATESBETWEEN ( Ecovisio[Date], DATE ( YEAR ( TODAY () ) - 1, 1, 1 ), DATE ( YEAR ( 'endDate' ) - 1, MONTH ( 'endDate' ), DAY ( 'endDate' ) ) ) )

Re: Between lastyear and same date of lastdate of this year

Lexarino — Thu, 28 Apr 2022 13:34:52 GMT

Seems to be the perfect solution but don't find the correct syntax to integrate it 😕

CALCULATE(SUM(EcoVisio[BIT Arzon]) + SUM(EcoVisio[BIT Vannes]),
var endDate = LASTDATE(EcoVisio[Date])
return
DATESBETWEEN(Ecovisio[Date], DATE(YEAR(TODAY())-1, 1, 1), DATE(YEAR('endDate') - 1, MONTH('endDate'), DAY('endDate')))
)

I'm such a looser in DAX !!

Re: Between lastyear and same date of lastdate of this year

Lexarino — Thu, 28 Apr 2022 13:40:48 GMT

OMG 😎 That's perfect !
And now i know how to construct variable in DAX.

I'm a PHP, JQuery, MySQL developper but every language has its subtleties !

Thanks a lot johnt75 !
A view of the result :

Re: Between lastyear and same date of lastdate of this year

Lexarino — Tue, 02 Aug 2022 08:49:26 GMT

Oh sorry i just find a new problem with this DAX command.

That's correct for the current year but i try this :

Ingenie - n-1 (total ventes) = VAR endDate = LASTDATE ( Ingenie[Date réservation] ) RETURN CALCULATE ( SUM(Ingenie[Prix total TTC Prestation (Vente)]), PARALLELPERIOD( DATESBETWEEN ( Ingenie[Date réservation], DATE ( YEAR ( TODAY () ), 1, 1 ), DATE ( YEAR ( 'endDate' ), MONTH ( 'endDate' ), DAY ( 'endDate' ) ) ) , -12, MONTH ) )

Try -1 Year and also SAMEPERIODLASTYEAR function but i identify this bug :

If the day in endDate variable is "2022-08-01" the function return amout of all the last year month of august, as if i had writen "2021-08-31".

Have you ever encountered this problem?

Thanks

Re: Between lastyear and same date of lastdate of this year

johnt75 — Tue, 02 Aug 2022 08:54:50 GMT

That's expected behaviour for PARALLELPERIOD, it automatically fills out any partial months. Try SAMEPERIODLASTYEAR instead

Re: Between lastyear and same date of lastdate of this year

Lexarino — Tue, 02 Aug 2022 09:08:52 GMT

I try this :

CALCULATE ( SUM(Ingenie[Prix total TTC Prestation (Vente)]), SAMEPERIODLASTYEAR( DATESBETWEEN ( Ingenie[Date réservation], DATE ( 2022, 1, 1 ), DATE ( 2022, 08, 01 ) ) ) )

Always the same problem, it accumulates the whole month of August of last year.
Not the same amount when i calculate directly 2021-1-1 to 2021-8-1

Re: Between lastyear and same date of lastdate of this year

johnt75 — Tue, 02 Aug 2022 09:17:42 GMT

Try running the below in DAX Studio

EVALUATE SAMEPERIODLASTYEAR( DATESBETWEEN ( Ingenie[Date réservation], DATE ( 2022, 1, 1 ), DATE ( 2022, 08, 01 ) ) ) ORDER BY Ingenie[Date réservation] DESC

and see what dates are returned. The problem could be that you are not using a proper Date table but are using the dates from your fact table, as when I run the above I get the correct dates ending with 1 August 2021

Re: Between lastyear and same date of lastdate of this year

Lexarino — Tue, 02 Aug 2022 12:25:17 GMT

Ohh thanks a lot !
You are right, in the DATESBETWEEN function I was referring to the calendar of the current table and not my reference calendar with unique date 😕 So stupid !
And as you said also, PARALLELPERIOD apply to all month.

So now i have the correct DAX :

VAR endDate = LASTDATE ( Ingenie[Date réservation] ) // Here the table calendar with data RETURN CALCULATE ( SUM(Ingenie[Prix total TTC Prestation (Vente)]), SAMEPERIODLASTYEAR( DATESBETWEEN ( Calendrier[Date], // Here the calendar with unique date DATE ( YEAR ( 'endDate' ), 1, 1 ), DATE ( YEAR ( 'endDate' ), MONTH ( 'endDate' ), DAY ( 'endDate' ) ) ) ) )