Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello ,
I have a requirement to bring the amount of previous year i.e. previous YTD where the business is 'Renewal', I am using the formula:
Let's assume you have the following in place:
Start with your base measure:
My Measure = SUM('My Fact Table'[Amount])
Add a Year To Date measure for it:
My YTD = TOTALYTD ( [My Measure], 'Dates'[Date] )
Add a 'Last Year' version:
My Last Year = CALCULATE ( [My Measure], SAMEPERIODLASTYEAR ( 'Dates'[Date] ) )
Finally, add the Last Year To Date:
My LYTD = TOTALYTD ( [My Last Year], 'Dates'[Date] )
To verify, create a Table visual and add 'Dates'[Date] first, then each of the measures above.
To simplify your verification porcess, maybe only show it at the month granularity.
You should see Dates ONLY for cases where it can create the measure. So if you have Dates dimension members going back to 1/1/1900, but facts only going back to 1/1/2020, your table visual should start at 1/1/2020.
Assuming you plot by the months, the first 12 months will have values for [My Measure] and [My YTD] but not the other two because DAX can't make that calculation. Then for January of 2021, the value for [My Last Year] should be the same as [My Measure] for January 2020.
Let us know how that goes.
Proud to be a Super User! | |
Hi, @Anonymous ;
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I am attaching the sample screenshot for review:
I have applied the filter on Year=2021 and New Business Renewal='RENEWAL'
The last column is RENPYTD which is blank where as the highlighted one is the desired result.
I am not able to attach a file as i dont have sufficient rights from this system.
Hi, @Anonymous ;
Here "12" is not right, according to the offical document,
PREVIOUSYEAR(<dates>[,<year_end_date>])
I tested a simple example.
Measure = CALCULATE(SUM('Table'[value]),'Table'[type]=1,PREVIOUSYEAR('Table'[Date],"12/31"))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ,
I have used the DAX provided by you:
Please advise.
Thanks
Try breaking your metric calculation into stages:
Now plot the two side-by-side in a TABLE visual with Year and Month. You can tell if it's working by comparing numbers across years.
Proud to be a Super User! | |
Hi @ToddChitt
Getting error while creating last year function:
"SAMEPERIODLASTYEAR function expects a contiguous selection when the date column is not unique, has gaps or it contains time portion." and too this function can contain max 1 argument.
You probably need a Dates (dimension) table joined to your measure table.
My Dates Table = CALENDAR("1/1/2020", "12/31/2022")
Now go back to the PolBudget table, find the DateTime field, and convert its Data Type to DATE (or create a calculated column that returns a DATE type) join PolBudget to 'My Dates Table' on that column. Reference 'My Dates Table'[Date] in the SAMEPERIODSLASTYEAR function.
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
107 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |