Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
panitapus
Frequent Visitor

YTD Last Year

I am trying to understand , How can one calcute LYTD in this case?  All i am trying to achieve is When i try to get Sales of last year i want the DAX to pull the MVPLastYearThisDate (nameof the column) value based on Original load Date (nameof the column)  and calculate.

Example: LYTD value for 01/03/2018 should be the sales value on 01/04/2017 where as LYTD for 01/05/2018 ,01/06/2018 and 01/07/2018 should be the sales on 01/06/2017.I have fact table with date and respective sales for that date. Any help is highly appriciated.

LastYearThisDate.JPG

LYTD.JPG

4 REPLIES 4
v-danhe-msft
Employee
Employee

Hi @panitapus,

Based on my test, you could refer to below steps:

Sample data

1.PNG

Create a calculated column in Fact Table:

Related column = RELATED(Table1[MVP LastYearThisDate])

4.PNG

Create the measure and you could see the result.

Measure = CALCULATE(SUM('Table2'[Today's Revenue]),DATESYTD('Table2'[Related column]))

5.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/shlgnhnaagibkm9/YTD%20Last%20Year.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I appreciate you response Daniel He. I followed all the steps and i still get totals based on OriginalLoadDate.I am looking for totals based on MVPLastYearThisDate in table 1.

Daniel- One to One relationship is not allowed, it is one to many.

Anonymous
Not applicable

@panitapus can you please post your power bi file.

 

Also try using parallelperiod to find year to date.

Parallel period cannot be used because i dont have specific interval on going back.I am looking for a dynamical way where it searches for MVPLastYearThisdate based on the OriginalLoadDate column and calculates.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.