cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Average for Previous Fiscal Year

Need help Calculating SUM, AVERAGE and COUNT of Claims Data for the Previous Fiscal Year (Ending June)

I have successfully created "Current Fiscal Year" Average

FY Current: Paid Claims Average =

TOTALYTD(

AVERAGE('Claims'[Actual]),

'Claims'[Completion Date],

'Claims'[Status of Claim] = "Paid",

"30/06"

)

I'm filtering by "Completion Date" and "Paid" Status.

I've tried using SAMEPERIODLASTYEAR with no success. I have a Date Dimension Table but Complete Date is not the same as another Date that has a relationship to the Date Table. I have a USERELATIONSHIP link but not sure whether I should be using this in my DAX Measure to a FY Field?

Any help on the best method to achieve this would be very much appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi @Jeremyh

You can try this measure with SAMEPERIODLASTYEAR DAX .

In your issue, I did not use the USERELATIONSHIP() function, everything seems to be working well

FY Previous: Paid Claims Average =

CALCULATE(AVERAGE('Claims'[Actual]),FILTER(Claims,'Claims'[Status of Claim] = "Paid"),SAMEPERIODLASTYEAR(DATESYTD( 'Claims'[Completion Date],"30/06")))

And the result value is shown in the screenshot

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Community Support

Hi @Jeremyh

You can try this measure with SAMEPERIODLASTYEAR DAX .

In your issue, I did not use the USERELATIONSHIP() function, everything seems to be working well

FY Previous: Paid Claims Average =

CALCULATE(AVERAGE('Claims'[Actual]),FILTER(Claims,'Claims'[Status of Claim] = "Paid"),SAMEPERIODLASTYEAR(DATESYTD( 'Claims'[Completion Date],"30/06")))

And the result value is shown in the screenshot

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III

Hi,

Share the link from where i can download your PBI file.  If possible, please show clearly in the PBI file, the answer that you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks for the reply, here is a link to my PBI File

The result I trying to get from "Previous Fiscal Year Average Claim Cost" is \$126.10 (For "Paid Claims" by "Completion Date" from "1 July 2019 - 30 June 2020")

Super User III

Hi,

I cannot in the first place understand your formula

FY Current: Paid Claims Average =
TOTALYTD(
AVERAGE('Claims'[Actual]),
'Claims'[Completion Date],
'Claims'[Status of Claim] = "Paid",
"30/06"
)
I do not know what the 'Claims'[Completion Date] is doing in the formula.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

This is the date use to evaluate "Year to Date". An alternative formula that also returns the correct result for Current YTD is

FY Current: Paid Claims Average v2 =
CALCULATE(
AVERAGE('Claims'[Actual]),'Claims'[Status of Claim] = "Paid",
DATESYTD('Claims'[Completion Date],"30/06")
)
Super User III

Hi,

Perhaps, it is my lack of understanding of the DAX formula language.  Your Data Table has a relationship with the appointment date column (not the completion date column).  So, to have any calculation run correctly, you must use the USERELATIONSHIP() function but i do not see you using that.  Furthermore, in the DATESYTD column, you should be referring to the Date Table.  Sorry but i cannot seem to help you here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

@Jeremyh , Try like

ytd=
TOTALYTD(
calculate(AVERAGE('Claims'[Actual]),userelationship('Claims'[Completion Date], 'Date'[Date]))
'Date'[Date],
'Claims'[Status of Claim] = "Paid",
"30/06"
)

Last ytd=
TOTALYTD(
calculate(AVERAGE('Claims'[Actual]),userelationship('Claims'[Completion Date], 'Date'[Date]))
'Claims'[Status of Claim] = "Paid",
"30/06"
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

Thank you for the quick Reply. Unfortunately your YTD returns a “Blank” and Last YTD returns a result that’s not quite correct. It’s probable that my data model isn’t ideal. I don’t believe that its necessary to use USERELATIONSHIP in my formula as the “Completion Date” which is what I’m grouping my FYTD Values by, is in the Claims Fact Table. My Original Current Fiscal Year does work correctly, it’s just the Previous Fiscal Year I can’t get correct. Thanks again, I appreciate any help.

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors