I have created a matrix visualization as attached in the screenshot. The first column is the Year and the following columns are different values calulated based on business formulae.
In addition to this visualization, there is also a difference view expected where the user can select 2 dates from the dropdown boxes (There is a Date column with format YYYYMM, which is part of the same table as used for calculation of different values in the matrix). Based on the selected dates the value to be shown is the difference in value till the first date minus difference in value to the second date.
Example - If drop down 1 has date selection as 201610 and dropdown 2 has date selection 201612, then in the difference view the value (let's say A) will be sum of value until 201612 minus sum of value until 201610.
I am relatively new to Power BI and have not worked much on DAX. Can someone please assist me with this? Thanks.
I'm a little confused about your scenario.
Do you want to create the dropdown boxes in matrix? If it is, I'm afraid that we cannot achieve that in power bi currently.
Do you want to create two dropdown slicers and calculate the difference of the value based on the selected date value?
If it is convenient, could you share some data sample which I can copy and your desired output so that we could help further on it?
There would be 2 dropdown boxes for the user to select dates (CORP_DATE is the column used for both drop-downs).
Based on the selected dates the difference in figures between the two selected dates should be displayed.
For example, if the user selects 201712 (i.e. December 2017) and 201810 (i.e. October 2018) as the two dates in the , then the difference report will show the inception-to-date figures through October 2018 minus the inception-to-date figures through December 2017.
"Do you want to create two dropdown slicers and calculate the difference of the value based on the selected date value?" - Yes, this is what I want based on inception to selected date values as mentioned above.
Am I clear now?
Do you have two date columns or one date column?
Do you want to create the list date slicer or other date slicer with the date column?
If it is convenient, could you share some data sample which could reproduce your scenario.
Apologies for this delayed response as I was away for a while.
There is just 1 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.