Calculate Average for Previous Fiscal Year

[Jeremyh]

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.

[v-yetao1-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[Jeremyh]

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

[Ashish_Mathur]

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.

[Jeremyh]

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

   )

[Ashish_Mathur]

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.

[amitchandak]

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

[Jeremyh]

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.