Most recent and oldest data within a specific time range using a slicer

[PatP]

Hi!

I’m trying to do a Push notification report with Power BI Desktop, but I can’t get what I want.

I have two tables:

1-A table with the split of device type per month (iOS vs Android) and with the % of devices with the push enabled that look like this :

Date	Device Family	Unique Devices	Opted In Users	Opted Out	Uninstalled
2018-04-01	android	65000	0	40000	31000
2018-04-01	ios	130000	51000	45000	15000
2018-03-01	android	105000	0	60000	66000
2018-03-01	ios	150000	85000	65000	51000

2-A table with a list of the push sent during the month with the number of devices that received the notification and opened it. The table look like this :

Push message	Date sent	Target	Target type	App notification sends	Direct app open
Alert 1	18-03-29	Area 1	1	100 000	50000
Alert 2	18-03-29	Area 2	1	99 000	49500
Alert 3	18-03-29	Area 3	1	84 562	42281
Alert 4	18-03-29	Area 4	1	321 651	160826
Alert 5	18-03-30	Area 4	2	161	81
Alert 6	18-03-30	Area 5	2	231 316	115658
Alert 7	18-03-31	Area 1	1	516	258
Alert 8	18-03-31	Area 2	1	51 616	25808
Alert 9	18-03-31	Area 1	1	16 515	8258
Alert 10	18-03-31	Area 2	1	321 684	160842
Alert 11	18-04-01	Area 1	3	35 165	17583
Alert 12	18-04-02	Area 2	3	213 516	106758
Alert 13	18-04-03	Area 3	3	231 665	115833
Alert 14	18-04-04	Area 4	3	21 666	10833
Alert 15	18-04-05	Area 5	3	29 662	14831
Alert 16	18-04-06	Area 1	1	69 463	34732
Alert 17	18-04-07	Area 2	2	51 968	25984
Alert 18	18-04-08	Area 3	1	684 651	342326
Alert 19	18-04-09	Area 4	2	655 161	327581
Alert 20	18-04-10	Area 3	1	68 432	34216

What I did and that is working right now :

-A matrix displaying the count of Push message per Target (rows) per Target type (column).
-Another table displaying the unique devices by device type (rows) per month (column).

What I try to do : A matrix (or table) that will show me the “App notification sends” per Target (rows) with three column: one displaying the first value (oldest) for dates selected and another one showing the most recent value during that specific time range. The third column displays % change. There would be only one slicer for the whole report. If I change the date in the slicer, the data change to get the oldest and most recent data for that specific time frame. The information displayed on other tables would also change.

Here is an example of what I would like to have for the whole month of march:

Date range: March
	App notification sends	App notification sends
Target	First sent	Last sent	% change
Area 1	100 000	16 515	-83%
Area 2	99 000	321 684	225%
…	…	…	…

I searched and found many ways to achieve this but only partly. Some way would not work with the slicer at all and display only the very first and last data from the whole table, other would apply the most recent/oldest filter to other table of the report… What would be the best way to do it?

[ChandeepChhabra]

@PatP As of now my solution sums up all the values on the last date and first date

Here is the snapshot of the result. It is summing up both the values for area 2 for march

You can download the pbix file here

[PatP]

@ChandeepChhabra Thank you for your help! It looks like what I'm looking for except for one thing : I would not sum the data, I would only display the last number. In my database I can get the date and time to make sure it's the very last push sent. How should we adjust your measure to show the first/last value only and not calculate the sum?

Thank you!

[ChandeepChhabra]

@PatP Hi, adding time is a good idea

After you refresh the pbi file, It will automatically ensure that the largest date (considering time) is picked up and measure will update automatically.

I just added random times to the dates, here is the result

Hope it helps

[PatP]

@ChandeepChhabra Thanks! This look like exactly what I'm looking for. The only thing I'm worried about, is if there is two notifications sent at exactly the same time, is there any way to take just the last entry? I would like to make sure I will never display a sum of different numbers.

Thank you again, I really appreciate it!

[ChandeepChhabra · Accepted solution]

Hey @PatP,

How would you define last entry. If the 2 sends are done on extactly the same time and date, which one should be considered the last?

If you would like to pick the maximum of them then replace the SUM with MAX in the 2 measures 

Last Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MAX(Push_Sent[Date sent])))

First Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MIN(Push_Sent[Date sent])))

[PatP]

Thank you, this is exactly what I was looking for! 

Thanks for your help!

[ChandeepChhabra]

You are welcome!

[ChandeepChhabra]

@PatP Quick question.. In the month of march for Area 2, you have 2 sends on the last day (31 Mar). Is there any logic of picking up 321644 and not 51616 ?

[PatP]

For privacy reasons, the actual data has been changed. The idea is to be able to estimate the actual number that have the push enabled by seeing the variation during the month.