Forum Discussion
Period over Period comparison
Hello Anonymous ,
Thank you for the reply. I would bring more info regarding the ask. It basically means,
The user wants to compare the most recent top 5 causes of loss to the same period last year. In other words, compare the top 5 causes for the first 7 months of 2021 to what they were for the first 7 months of 2020 for both severity and frequency.
- I exported two of Fox Rehab valuation files from ClaimsLens as the basis for this example
- For the 7.15.21 file I deleted any claims with a loss date not in 2021. We only want to look at YTD claims in 2021
- For the 7.15.20 file I deleted any claims with a loss date not in 2020. Again, we want to look at YTD Claims and only for 2020
- Determined the Top 5 Causes of loss for severity in the 2021 file. These are the top 5 we want to compare to the 2020 file
- Determined the Top 5 Causes of loss for Frequency in the 2021 File. These are the top 5 we will compare to the 2020 file
Used Pivot tables to determine values and mocked up the below comparison Chart.
Hi Anonymous,
Did you mean to rank and pick the top 5 categories based on the current date filter and show them on a chart and groups by categories, split by date legends, right?
If that is the case, you may need to do the following steps.
1. Create an unconnected date table, it will be used as legends on the chart.
2. Drag the Category field to the axis of the chart.
3. Write a measure expression and use it on the value fields of your chart.
For the measure formula:
1. It needs a variable to summary table records and ranking records based on date and causes then you can extract the match causes to a list.
All the secrets of SUMMARIZE - SQLBI
2. It required if statement to check the filtered date if they are included in the last three years with the current month.
3. It required if statement to check if the current cause is included in the list of ranked cause list.
4. If current values matched all the conditions, you can use the current date and current cause as conditions to filter and summary calculated results.
Regards,
Xiaoxin Sheng
Hello Anonymous
Thans for the reply, i will certainly try to follow the method mentioned. But just to be more clear, what i am looking for is I have a slicer for valuation dates which has 2 dates in it. 7-15-2020 and 7-15-2021. Both these valuation dates have a loss date range separtly starting from 01-01-2018 till end of 2021 respectively. What I am looking for is, I need to find the top 5 causes of loss for the most recent valuation date from 01-01-2021 till loss range till 07-15-2021 and the same top 5 causes of loss for the 07-15-2021 but the loss data should be the 01-01-2020 till 07-15-2020. Basically showing the top 5 causes of loss for the 2021 validation date with same top 5 causes of 2021 but values of loss raning from 1-1-2020 till 7-15-2020.
Hope i was clear. I would like to know the Dax queries to use in order to achieve it.
Thanks,
gladson