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
Super User

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
Super User

@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 !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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