cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeremyh
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?

 

Jeremyh_0-1623993987782.png

 

 

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

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
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

Ailsamsft_0-1624267042393.png

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.

View solution in original post

8 REPLIES 8
Ailsa-msft
Community Support
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

Ailsamsft_0-1624267042393.png

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.

View solution in original post

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

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

https://drive.google.com/file/d/1ed-Q2tnC6qbyBXIFBg84wJ-IXB6OHxe-/view?usp=sharing

 

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

 

Jeremyh_0-1624149089754.png

 

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
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User IV
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]))
dateadd('Date'[Date],-1,year),
'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!

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors