topic Re: LYTD (last year, month and day) in DAX Commands and Tips

LYTD (last year, month and day)

FR14 — Tue, 05 Mar 2024 22:35:55 GMT

Hi Everyone,

What formula i can use to get same amount of orders last year, month and day? for example last year 06.03.2023 amount.

My current formula is this but this but it doesn't work correctly as it shows me LYTD full month! and not up to 06.03.2023

LY YTD Bookings= VAR __MaxDate = MAX(Dates[Date])

RETURN

Calculate(SUM(OrderLine[Booking USD]), YEAR('OrderLine'[Open_Date]) = YEAR(__MaxDate)-1, MONTH('OrderLine'[Open_Date]) <= MONTH(__MaxDate ))

Thank you all very much for your help in advance!

Re: LYTD (last year, month and day)

v-weiyan1-msft — Wed, 06 Mar 2024 02:10:52 GMT

Hi FR14 ,

Based on your description, here is the dummy data I created.
OrderLine:

Dates:

Please try code as below.

LY YTD Bookings = VAR _MaxDate = MAX(Dates[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) )

Or you can also consider using the following code.

Previous YTD = CALCULATE ( SUM ( 'OrderLine'[Booking USD] ), DATESYTD ( DATEADD ( 'OrderLine'[Open_Date], -1, YEAR ) ) )

Result is as below.

Is this the result you expect?
Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: LYTD (last year, month and day)

lbendlin — Wed, 06 Mar 2024 03:01:56 GMT

SAMEPERIODLASTYEAR combined with an EDATE(xxx,-12) limiter is the safest bet.

Re: LYTD (last year, month and day)

FR14 — Wed, 06 Mar 2024 15:24:55 GMT

Thank you for the idea! would be able please to help me put this in my formula? i would really apprecaite your support.

LY YTD Bookings= VAR __MaxDate = MAX(Dates[Date])

RETURN

Calculate(SUM(OrderLine[Booking USD]), YEAR('OrderLine'[Open_Date]) = YEAR(__MaxDate)-1, MONTH('OrderLine'[Open_Date]) <= MONTH(__MaxDate ))

Re: LYTD (last year, month and day)

lbendlin — Wed, 06 Mar 2024 15:26:42 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: LYTD (last year, month and day)

FR14 — Wed, 06 Mar 2024 15:27:07 GMT

Thank you so much for the efforts to support! i tried first method but when i leave the filter on default (all months) it doesn't work correctly. I want this to be able to show LYTD totals and then when i select March it only shows March values. Do you think there is anyway to do this?

Re: LYTD (last year, month and day)

FR14 — Wed, 06 Mar 2024 15:49:35 GMT

Unfortunately i cannot provide sample data as it's senstive, do you think you could tell me approx where to put this in my formula to get LYTD specifc month amounts?

LY YTD Bookings= VAR __MaxDate = MAX(Dates[Date])

RETURN

Calculate(SUM(OrderLine[Booking USD]), YEAR('OrderLine'[Open_Date]) = YEAR(__MaxDate)-1, MONTH('OrderLine'[Open_Date]) <= MONTH(__MaxDate ))

Re: LYTD (last year, month and day)

lbendlin — Wed, 06 Mar 2024 15:51:22 GMT

Sorry, I cannot assist without meaningful sample data. I hope someone else can help you further.

Re: LYTD (last year, month and day)

FR14 — Thu, 07 Mar 2024 04:45:50 GMT

Everything works okay for the month total but for complete LYTD total it doesn't add up correct and total is wrong:( do you know how we can solve this?

Re: LYTD (last year, month and day)

v-weiyan1-msft — Thu, 07 Mar 2024 05:13:47 GMT

Hi FR14 ,

I am not sure if I understood your question correctly.
Can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,
Yulia Yan

Re: LYTD (last year, month and day)

FR14 — Thu, 07 Mar 2024 05:20:00 GMT

Your first method works correctly to have exact value for LYTD March, Jan & Feb but when i check the total LYTD Jan,Feb & March the total doesn't sum up correctly.