Show measure for previous year figure when filtering for current year

[TenguMan]

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!

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[lbendlin]

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

[TenguMan]

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

[lbendlin]

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.

[TenguMan]

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.

[lbendlin]

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

[TenguMan]

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.

[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. 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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...