topic Show measure for previous year figure when filtering for current year in DAX Commands and Tips

Show measure for previous year figure when filtering for current year

TenguMan — Fri, 07 Mar 2025 19:35:08 GMT

Hello,

I have a measure I am using to look for the average restoration time of incidents for the previous fiscal year, and it works for its intended purpose, but I would like for it to display when I am looking at the current fiscal year. How should I adjust the measure to display the same information that it currently does when looking for dates in the current fiscal year? The measure is as follows:

Time to Restore_2024 = CALCULATE([Mean Time to Restore],FILTER(Incidents,Incidents[Actual start of incident]>=2023/11/01&&Incidents[Actual start of the incident]<=(NOW()-365)))

Currently, this displays a value that's in line with what I'm expecting when I filter for dates in the previous fiscal year. I would like for it to display the same information when filtering for dates only in the current fiscal year; it currently returns a blank if I do that (which is understandable given the way I've written this). Any assistance would be greatly appreciated!

Re: Show measure for previous year figure when filtering for current year

lbendlin — Sat, 08 Mar 2025 02:14:58 GMT

If your data model has a proper Calendar table then you can use the standard time intelligence functions like SAMEPERIODLASTYEAR()

Re: Show measure for previous year figure when filtering for current year

TenguMan — Mon, 10 Mar 2025 13:14:56 GMT

Will this still work if the period in question is across two calendar years? The fiscal year begins in November for this model, so I would be looking at data from November to February (ultimately November to October).

Re: Show measure for previous year figure when filtering for current year

lbendlin — Mon, 10 Mar 2025 13:20:20 GMT

yes. It ony breaks down for fiscal quarters where you have to fall back to the underlying DATEADD(xxx,-3,MONTH) function. Comparing adjacent fiscal quarters is futile anyhow.

Re: Show measure for previous year figure when filtering for current year

TenguMan — Mon, 10 Mar 2025 19:16:48 GMT

To make sure, the function I would change it to would look something like this, right?

Time to Restore_2024 = CALCULATE([Time to Restore],FILTER(Incidents,SAMEPERIODLASTYEAR(Incidents[Incident Day]))

Assuming a relationship to the calendar table.

Re: Show measure for previous year figure when filtering for current year

lbendlin — Mon, 10 Mar 2025 21:16:09 GMT

SAMEPERIODLASTYEAR needs to go against the dimension date, not the fact date.

Re: Show measure for previous year figure when filtering for current year

TenguMan — Tue, 11 Mar 2025 14:15:48 GMT

I tried this with the date key column from both the calendar and the incidents table, and it is not working as I expected. When I look at fiscal year 2025 dates, it shows 2025 data (expecting 2024); when I look at fiscal year 2024 dates, nothing happens.

Re: Show measure for previous year figure when filtering for current year

lbendlin — Tue, 11 Mar 2025 16:42:48 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. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Show measure for previous year figure when filtering for current year

v-sgandrathi — Mon, 28 Apr 2025 07:24:57 GMT

Hi TenguMan,

Super User has already highlighted -- kindly requesting you to share sample data so that we can assist more accurately and provide the best solution.

If your query has been resolved, please feel free to share the solution here. It will help others in the community to resolve similar threads more easily.

Thank you.

Re: Show measure for previous year figure when filtering for current year

v-sgandrathi — Mon, 05 May 2025 04:39:07 GMT

Hi TenguMan,

I wanted to follow up on our previous rwply regarding the issue. We would love to hear back from you to ensure we can assist you further.

If you got the solution, Kindly share with the community members and please accept it as a solution so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

Re: Show measure for previous year figure when filtering for current year

v-sgandrathi — Sat, 14 Jun 2025 05:49:21 GMT

Hi TenguMan,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.