Forum Discussion
Period over Period comparison
Hi,
I need to display just 9 months of data for each year for 2018, 2019 and 2020 to compare frequencey for top 5 causes.
.i.e., i need to display data in a chart for date starting from 01/01/2018 to 09/30/2020.
- For instance, for the 9/30/2018 bar includes all claims that occurred from 01/01/2018 through 09/30/2018 and valued as of 9/30/2018.
- For the 9/30/2019 bar include all claims that occurred from 01/01/2019 through 09/30/2019 and valued as of 9/30/2019
- For the 9/30/2020 bar include all claims that occurred from 01/01/2020 through 09/30/2020 and valued as of 9/30/2020
Please find the ss of the data to be displayed in this format.
how to achive this?
what would be the right visualization chart to display the data in the below attached format?
8 Replies
You would use a calendar table in your data model. That table would have a month number column. You would then filter that column to only include months 1 through 9.
Hi Anonymous,
I'd like to suggest you create a calculated table with the date value you required and use it on the legend field, then you can write measure expressions to extract the current category and redirect to particular calculate formulas that filter by the current legend:
If the above does not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
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 ,
Can you please help me achieve the ask. Please follow the SS first
I need to display the values under MININCURRED for valuation Date 07/15/2020 beside valuation date 07/15/2021. I need to elimiate the values displayed under valuation date 07/15/2021 under MININCURRED i.e, for valuation date 07/15/2021, MAXINCURRED is 82,039 and MININCURRED should be 879 for the cause Stain or Injury by Lifting and following the same for the rest.
Let me tell you the Dax queires used. :
MAXVAL : MAXVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))
MAXINCURRED:MAXINCURRED =
CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
('clm ValuationData'[MAXVAL])))MINVAL: MinVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
PREVIOUSYEAR(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))MININCURRED =
CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
'clm ValuationData'[MinVAL]))If the used Dax is not upto the mark, please help me achieve the above ask if there are any other methods.
Please help.
Thanks,
gladHi Anonymous,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
