Period over Period comparison

[Anonymous]

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. 

1. 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.
2. 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
3. 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? 

[Anonymous]

Hello @v-shex-msft ,

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, 
glad

[v-shex-msft]

Hi @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

[v-shex-msft]

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:

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup.com)

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

[Anonymous]

Hello @v-shex-msft ,

Can i have some update? 

To breif you more about it. I need to compare Top 5 causes of Loss Range for the year 2021 with Same top 5 causes of 2021 but the loss range year would be 2020. 

[Anonymous]

Hello @v-shex-msft , 

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. 

1. I exported two of Fox Rehab valuation files from ClaimsLens as the basis for this example
2. 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
3. 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
4. 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
5. 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. 

[v-shex-msft]

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

Sorting by multiple columns 

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

[Anonymous]

Hello @v-shex-msft 

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 

[lbendlin]

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.