Hi,
I've had a search to try and work this out, but none of the other posts are quite the same, so hoping someone can help please.
I want to allow users to select 2 date ranges (eg from slicers) so they can compare which of our clients were New/Retained/Lost in date range 2.
@Ashish_Mathurwas very helpful to me last month supplying some DAX to help me create a similar scenario - that time it was using a predefined date range in the DAX for New/Lost/Retained. That is working well for my users that are able to use the predefined dates. I have included the DAX at the end of this post in case it can be tweaked for this new scenario.
My new brief is to allow some users to select their own ranges to compare. e.g. They may wish to compare clients who spent with us in Jan-Mar 2019 vs Apr-Jun 2019. Or all of 2017 vs all of 2018. Basically any range they wish to see at the time. They will select the start and end date of each range.
I envisage I could have 2 sets of slicers on the page - one set links to table 1 and does not interact with table 2. And vice versa for the 2nd set of slicers. Then perhaps in a 3rd table it shows the results:
Lost - clients that appear in table/range 1 but not in table/range 2.
New - clients that appears in table/range 2 but not in table/range 1.
Retained - clients that appear in both tables/ranges.
The client and revenue spend information is all on one data source/table at present, and I use a linked calendar table in slicers usually.
I'm open to the way it's displayed on the page - the above scenario is just to explain a desired outcome. Ultimately I just need to be able to select the start and end date of 2 ranges, then compare the data to see the name of the clients and whether they are N/L/R.
Some sample data is linked here: https://1drv.ms/u/s!AvZLa7VG1kXrgUoAfZjuRIzN1T8Y?e=paGP0L
From this sample data if I were to compare 2017 to 2018, client BBB would be Lost and Client AAA would be new.
If I were to compare Jan-Jun 2019 to Jul-Dec 2019 I would expect the following:
Client DDD is retained.
Client CCC is lost.
Clients AAA, BBB and FFF are new.
Thanks very much in advance!
Here's the DAX from the pre-defined ranges in case it's of use in this scenario.
NewBusiness =
COUNTROWS (
FILTER (
SUMMARIZE (
VALUES ( 'Data'[ClientName] ),
'Data'[ClientName],
"ABCD", [Revenue],
"EFGH", CALCULATE (
[Revenue],
DATESBETWEEN (
'CalendarDate'[FullDate],
EDATE ( MIN ( 'CalendarDate'[FullDate] ), -12 ),
MIN ( 'CalendarDate'[FullDate] ) - 1
)
)
),
[ABCD] > 0
&& [EFGH] = 0
)
)
RetainedBusiness =
COUNTROWS (
FILTER (
SUMMARIZE (
VALUES ( 'Data'[ClientName] ),
'Data'[ClientName],
"ABCD", [Revenue],
"EFGH", CALCULATE (
[Revenue],
DATESBETWEEN (
'CalendarDate'[FullDate],
EDATE ( MIN ( 'CalendarDate'[FullDate] ), -12 ),
MIN ( 'CalendarDate'[FullDate] ) - 1
)
)
),
[ABCD] > 0
&& [EFGH] > 0
)
)
Lost Business =
COUNTROWS (
FILTER (
SUMMARIZE (
CALCULATETABLE (
VALUES ( 'Data'[ClientName] ),
DATESBETWEEN (
'CalendarDate'[FullDate],
MINX ( ALL ( 'CalendarDate' ), 'CalendarDate'[FullDate]),
EDATE ( MIN ( 'CalendarDate'[FullDate] ), -12 ) - 1
)
),
'Data'[ClientName],
"ABCD", [Revenue],
"EFGH", CALCULATE (
[Revenue],
DATESBETWEEN (
'CalendarDate'[FullDate],
EDATE ( MIN ( 'CalendarDate'[FullDate] ), -12 ),
MIN ( 'CalendarDate'[FullDate] ) - 1
)
),
"IJKL", CALCULATE (
[Revenue],
DATESBETWEEN (
'CalendarDate'[FullDate],
MINX ( ALL ( 'CalendarDate' ), 'CalendarDate'[FullDate] ),
EDATE ( MIN ( 'CalendarDate'[FullDate] ), -12 ) - 1
)
)
),
[ABCD] = 0
&& [EFGH] = 0
&& [IJKL] > 0
)
)
