Display 1 row of customer data from 2 different dates where dates are selected dynamically by user

[ChicagoPBI_noob]

I have a tricky one I need help solving.  I have 1 data table that archives customer information every day. Each customer has a unique identifier, however, due to the daily archiving, this identifier is only unique when concatenated with the date.  What I'm trying to do is display each customer on 1 row with information from 2 different dates.  The ask from the 'user' is to see how the customer migrated for a specific metric from date 1 to date 2.  So as an example, Customer A will be displayed on 1 row and the columns displayed will be: Customer Name, Date 1, Metric (as of date 1), Date 2, Metric (as of date 2).  Customer B will be displayed on the next row and so on...  Keep in mind, the Date 1 and Date 2 needs to be selectable by the users.  So a user should be able to open the report and select 2 different dates and the report will update and display the information as of the 2 dates selected by the user.  I have been able to do this in a different reporting tool, so I can explain how if that helps.

[v-jianhe-msft]

Hi,

Please provide sample datasource/tables used, and the expected result you hope to get using screenshot. You could also share the report via OneDrive link.

Best Regards,

Henry 

[Greg_Deckler]

Please provide sample/example data and expected output. Lots of words in there.

[ChicagoPBI_noob]

Thanks for your suggestion.  I have put together an excel file with 2 tabs.  The 1st tab is a representation of the raw data being linked/referenced in the report.  The 2nd tab is the desired output.  However, I'm unsure how to attach it here.  Please advise.

Thanks,

[Greg_Deckler]

Depending on how much data, and you generally do not need a lot of it, you could just copy the data from Excel and paste it. Otherwise, upload the Excel file to a cloud-based repository like OneDrive or Box and create a link for it and then paste that link here.

[ChicagoPBI_noob]

Desired Output would look something like this:

Customer_ID	Customer_Name	Date_1	Customer_Rating_1	Date_2	Customer_Rating_2	Rating_Change
A123456789	AAA_Corp	4/30/2017	1	5/31/2017	2	-1
B123456789	BBB_Corp	4/30/2017	4	5/31/2017	5	-1
C123456789	CCC_Corp	4/30/2017	1	5/31/2017	2	-1
D123456789	DDD_Corp	4/30/2017	4	5/31/2017	5	-1

[ChicagoPBI_noob]

The user will select 2 dates, obviously based on my output data, I selected date_1 = 4/30/2017 & date_2 = 5/31/2017

[Greg_Deckler · Accepted solution]

OK, I *think* what you want to do is to create 2 slicers and remove all of their cross-filtering to any other tables. Then, you can use SELECTEDVALUE for each slicer to construct a measure that reports back the rating change. This measure would simply filter your table based upon your first date and return the value. You would use a VAR for this. You would then use another VAR to filter your table by your other SELECTEDVALUE and get that rating. Then just RETURN the difference.

[ChicagoPBI_noob]

Thanks for your response. Your solution worked.  However, I had another power user actually do the work since it was above my head.  Here's the formula we used:

DDifference =
    VAR date_1 = SELECTEDVALUE(qry_Monthly_FAC_AllDates[Date])
    VAR date_2 = SELECTEDVALUE('qry_Monthly_FAC_AllDates (2)'[Date])
    RETURN
    (CALCULATE(MAX(qry_Monthly_FAC_AllDates[Final Risk Rating]), FILTER(qry_Monthly_FAC_AllDates, qry_Monthly_FAC_AllDates[Date] = date_1)) - CALCULATE(MAX('qry_Monthly_FAC_AllDates (2)'[Final Risk Rating]), FILTER('qry_Monthly_FAC_AllDates (2)', 'qry_Monthly_FAC_AllDates (2)'[Date] = date_2)))

[ChicagoPBI_noob]

In the desired output, there will also need to be 2 date selectors for the users to select the 2 dates they want to see.

[ChicagoPBI_noob]

Raw Data Sample:

Customer_ID	Customer_Name	Date	Customer_Rating
A123456789	AAA_Corp	1/31/2017	1
A123456789	AAA_Corp	2/28/2017	1
A123456789	AAA_Corp	3/31/2017	1
A123456789	AAA_Corp	4/30/2017	1
A123456789	AAA_Corp	5/31/2017	2
A123456789	AAA_Corp	6/30/2017	2
A123456789	AAA_Corp	7/31/2017	1
A123456789	AAA_Corp	8/31/2017	1
A123456789	AAA_Corp	9/30/2017	2
A123456789	AAA_Corp	10/31/2017	3
A123456789	AAA_Corp	11/30/2017	3
A123456789	AAA_Corp	12/31/2017	4
B123456789	BBB_Corp	1/31/2017	2
B123456789	BBB_Corp	2/28/2017	2
B123456789	BBB_Corp	3/31/2017	4
B123456789	BBB_Corp	4/30/2017	4
B123456789	BBB_Corp	5/31/2017	5
B123456789	BBB_Corp	6/30/2017	2
B123456789	BBB_Corp	7/31/2017	3
B123456789	BBB_Corp	8/31/2017	1
B123456789	BBB_Corp	9/30/2017	4
B123456789	BBB_Corp	10/31/2017	5
B123456789	BBB_Corp	11/30/2017	5
B123456789	BBB_Corp	12/31/2017	4
C123456789	CCC_Corp	1/31/2017	2
C123456789	CCC_Corp	2/28/2017	4
C123456789	CCC_Corp	3/31/2017	5
C123456789	CCC_Corp	4/30/2017	1
C123456789	CCC_Corp	5/31/2017	2
C123456789	CCC_Corp	6/30/2017	1
C123456789	CCC_Corp	7/31/2017	2
C123456789	CCC_Corp	8/31/2017	3
C123456789	CCC_Corp	9/30/2017	4
C123456789	CCC_Corp	10/31/2017	5
C123456789	CCC_Corp	11/30/2017	2
C123456789	CCC_Corp	12/31/2017	1
D123456789	DDD_Corp	1/31/2017	5
D123456789	DDD_Corp	2/28/2017	5
D123456789	DDD_Corp	3/31/2017	4
D123456789	DDD_Corp	4/30/2017	4
D123456789	DDD_Corp	5/31/2017	5
D123456789	DDD_Corp	6/30/2017	5
D123456789	DDD_Corp	7/31/2017	4
D123456789	DDD_Corp	8/31/2017	4
D123456789	DDD_Corp	9/30/2017	3
D123456789	DDD_Corp	10/31/2017	4
D123456789	DDD_Corp	11/30/2017	4
D123456789	DDD_Corp	12/31/2017	3