Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @panitapus,
Based on my test, you could refer to below steps:
Sample data
Create a calculated column in Fact Table:
Related column = RELATED(Table1[MVP LastYearThisDate])
Create the measure and you could see the result.
Measure = CALCULATE(SUM('Table2'[Today's Revenue]),DATESYTD('Table2'[Related column]))
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
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.
@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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |