cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vishy86 Regular Visitor
Regular Visitor

Matrix Visualization - Difference View

There is a date column CORP_PERIOD_DT. End user will have 2 date selections (could be anything slicer, list, whatever is feasible). Both these date selections refer to the same column CORP_PERIOD_DT.

 

Please refer the attached screenshot. There are 2 date selection fields as shown. Right now you only see one date value because it is sample data for 1 month for testing purposes.

 

The current view is with Direct Premium, Direct Earned Premium and other parameters calculated based on business formula.

 

The desired result is the same matrix (without the Ins Prog Yr column) but with all the other parameters AND will have just 1 row with values. The values will be the difference between Inception to Date (cumulative sum) till the date selected in first slicer and the Inception to Date (cumulative sum) till the date selected in second slicer.

 

Example - If the user selects 2 dates viz. 20180706 (6th Jul 2018) and 20190103 (3rd Jan 2019) then the Direct Written Premium difference value as shown in matrix would be inception to date figures through 3rd Jan 2019 minus the inception to date figures through 6th Jul 2018.

So, if Direct Written Premium till 6th Jul 2018 is 100 and till 3rd Jan 2019 is 300 then the value shown in the desired result would be 200.

 

PS: All the columns Direct Written Premium, Direct Earned Premium etc. are part of the same view that the report is pointing to. CORP_PERIOD_DT is also part of the same view.

 

Let me know if you have more questions.Difference View.PNG

3 REPLIES 3
Super User
Super User

Re: Matrix Visualization - Difference View

I don't think you are going to get there with just one column. I would create a date table with a [Date] column and a duplicate column, [Date - Copy]. Then I would create 2 slicers and your visual, all disconnected from one another and then write a measure that essentially does something like: Measure = VAR __date1 = MAX('DateTable'[Date]) VAR __date2 = MAX('DateTable'[Date1 - Copy]) RETURN IF('Table'[Date] >= __date1 && 'Table'[Date] <= __date2),1,0) Then you could filter based upon this.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


vishy86 Regular Visitor
Regular Visitor

Re: Matrix Visualization - Difference View

Hi Greg, I was trying to use DAX referring the following link - https://stackoverflow.com/questions/50896429/power-bi-running-totals-with-filters-and-slicers?rq=1 I guess the above link is similar to my scenario in current project. I am relatively new to DAX hence would appreciate some guidance and help on tackling this. Thanks, Vishy
vishy86 Regular Visitor
Regular Visitor

Re: Matrix Visualization - Difference View

Hi Greg, I created the below measure. Measure = CALCULATE( SUM('Historical Profitability'[WRITTEN_PREMIUM_AT]), FILTER(ALL('Historical Profitability'[CORP_PERIOD_DT]),'Historical Profitability'[CORP_PERIOD_DT] <= MAX('Historical Profitability'[CORP_PERIOD_DT]))) Please help with the same. Is this possible using DAX? Regards, Vishy