LYTD (last year, month and day)

[FR14]

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!

[lbendlin]

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

[FR14]

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

[lbendlin]

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

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/1447...

[FR14]

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

[lbendlin]

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

[v-weiyan1-msft]

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.

[FR14]

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?

[v-weiyan1-msft]

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

[FR14]

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. 

[FR14]

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?