Between lastyear and same date of lastdate of this year

[Lexarino]

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 !

[johnt75 · Accepted solution]

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

[Lexarino]

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

[johnt75 · Accepted solution]

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

[Lexarino]

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

[johnt75]

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

[Lexarino]

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

[johnt75 · Accepted solution]

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

[Lexarino]

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

[Lexarino]

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 : 

[johnt75]

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

[Lexarino]

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