topic Re: LY YTD (total) in DAX Commands and Tips

LY YTD (total)

FR14 — Sat, 09 Mar 2024 08:44:29 GMT

Hi Everyone,

Using following formula below, i am able to get the LY YTD for the exact month and day amount BUT when i fliter all the months the total LY YTD doesn't show correct amount, do you guys know what condition should be added to this DAX to also have the total LY YTD correct when we filter all the months?

LY YTD Bookings =

VAR _MaxDate = MAX(OrderLine[Open_Date])

VAR _PreviousYear = YEAR(_MaxDate) - 1

RETURN

CALCULATE(

SUM(OrderLine[Booking USD]),

YEAR(OrderLine[Open_Date]) = _PreviousYear,

MONTH(OrderLine[Open_Date]) <= MONTH(_MaxDate),

DAY(OrderLine[Open_Date]) <= DAY(_MaxDate)

)

Thank you all so much for your support.

Re: LY YTD (total)

Jihwan_Kim — Sat, 09 Mar 2024 12:19:38 GMT

Hi,

I am not sure how your semantic model looks like, but I suggest using Calendar Dimension table to alter filter context.

But, by using only the situation shown here, please try something like below whether it produces the expected result.

LY YTD Bookings = VAR _MaxDate = MAX ( OrderLine[Open_Date] ) VAR _PreviousYear = YEAR ( _MaxDate ) - 1 RETURN CALCULATE ( SUM ( OrderLine[Booking USD] ), REMOVEFILTERS ( OrderLine[Open_Date] ), YEAR ( OrderLine[Open_Date] ) = _PreviousYear, MONTH ( OrderLine[Open_Date] ) <= MONTH ( _MaxDate ), DAY ( OrderLine[Open_Date] ) <= DAY ( _MaxDate ) )

Re: LY YTD (total)

FR14 — Sun, 10 Mar 2024 10:00:35 GMT

Thank you but it didn't solve my problem unfotunately still the amount for the LY YTD to this day of the month is showing correct but then whe select JAN,FEB & March last year the total is not corrrect 😞

Re: LY YTD (total)

StrategicSavvy — Sun, 10 Mar 2024 16:19:33 GMT

hi FR14

I belive you can use DATESYTD function based on your calendar table. Make sure you have properly built di calendar table as Jihwan_Kim mentioned before you go.

First create measure that will make you are not showing values for future dates in your calendar table:

ShowValueForDates := VAR LastDateWithData = CALCULATE ( MAX ( OrderLine[Open_Date] ), REMOVEFILTERS () ) VAR FirstDateVisible = MIN ( 'Date'[Date] ) VAR Result = FirstDateVisible <= LastDateWithData RETURN Result

Secondly create this measure:

LY YTD Bookings = IF( [ShowValueForDates], CALCULATE( SUM ( OrderLine[Booking USD] ), DATESYTD('Date'[Date]) ) )

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron

Re: LY YTD (total)

Anonymous — Fri, 15 Mar 2024 08:34:33 GMT

Hi FR14 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng