topic Re: LY YTD Measure in DAX Commands and Tips

LY YTD Measure

FR14 — Thu, 18 Jan 2024 10:57:02 GMT

Hello Everyone,

This is my measure for LY YTD but it shows complete year values, how can i modify it to show the last year year to date values?

LY YTD Bookings Annualized =

VAR __MaxDate = MAX('Transaction Date'[Date])

RETURN

Calculate(SUM(Orders[Annualized Value]), YEAR('Transaction Date'[Date]) = YEAR(__MaxDate)-1, MONTH('Transaction Date'[Date]) <= MONTH(__MaxDate ))

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 11:07:06 GMT

FR14 You can modify the logic this way. This will give LY YTD values.

LY YTD Bookings Annualized = VAR __MaxDate = MAX('Transaction Date'[Date]) VAR __StartDateLastYear = DATE(YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate)) RETURN CALCULATE( SUM(Orders[Annualized Value]), 'Transaction Date'[Date] >= __StartDateLastYear && 'Transaction Date'[Date] <= __MaxDate )

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 11:16:08 GMT

FR14 If your there is date table in the model, you can use time intelliegence functions like DATEADD , SAMEPERIODLASTYEAR for LY YTD calculations.

Re: LY YTD Measure

FR14 — Thu, 18 Jan 2024 11:16:51 GMT

Thank you very much but result show me this year values when i implement it.

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 11:22:48 GMT

FR14 You can try this

LY YTD Bookings Annualized = VAR __MaxDate = MAX('Transaction Date'[Date]) VAR __StartDateLastYear = DATE(YEAR(__MaxDate)-1, 01, 01) RETURN CALCULATE( SUM(Orders[Annualized Value]), 'Transaction Date'[Date] >= __StartDateLastYear && 'Transaction Date'[Date] <= __MaxDate )

Re: LY YTD Measure

FR14 — Thu, 18 Jan 2024 11:25:51 GMT

Thank you i checked this one and it shows complete last year values not last year January.

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 11:29:37 GMT

FR14 Did you got what you where looking ?

Re: LY YTD Measure

FR14 — Thu, 18 Jan 2024 11:33:40 GMT

Not really, it still shows complete last year data not last year January.

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 11:38:24 GMT

FR14 You can try this, this will give for last year january to date

LY YTD Bookings Annualized = VAR __MaxDate = MAX('Transaction Date'[Date]) VAR __StartDateLastYear = DATE(YEAR(__MaxDate)-1, 01, 01) VAR __Enddate = DATE(YEAR(__MaxDate)-1, 01, DAY(__MaxDate)) RETURN CALCULATE( SUM(Orders[Annualized Value]), 'Transaction Date'[Date] >= __StartDateLastYear && 'Transaction Date'[Date] <= __Enddate )

Re: LY YTD Measure

FR14 — Thu, 18 Jan 2024 14:00:29 GMT

Thank you for your support, this solution did not solve my problem. It works okay for this YTD but when i select other months and years values don't change

Re: LY YTD Measure

saurabhtd — Thu, 18 Jan 2024 14:09:04 GMT

FR14 You can try this

LY YTD Bookings Annualized = VAR __MaxDate = MAX('Transaction Date'[Date]) VAR __StartDateLastYear = DATE(YEAR(__MaxDate)-1, 01, 01) VAR __Enddate = DATE(YEAR(__MaxDate)-1,MONTH(__MaxDate), DAY(__MaxDate)) RETURN CALCULATE( SUM(Orders[Annualized Value]), 'Transaction Date'[Date] >= __StartDateLastYear && 'Transaction Date'[Date] <= __Enddate )