Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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")
Hi,
I cannot in the first place understand your formula
This is the date use to evaluate "Year to Date". An alternative formula that also returns the correct result for Current YTD is
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.
@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"
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |