How do I add a user input fields and compare the values related to input

[hansel]

Hi,

I have a data for monthly prepaid subscription of a tool, I need to add a user input field in report wherein the user would enter two months he/she would like to compare, to show the net new subscribers in latest month of the two months entered, as well as the subscribers from the earlier month of the two months who haven't renewed in the latest month and the subscribers present in both months.

Here is some sample data

month	Customer No	Amt Paid
1	101	500
1	145	1000
1	123	1200
1	185	250
1	146	2000
1	126	500
1	189	500
1	147	500
2	101	500
2	145	500
2	185	500
2	189	1000
2	201	1200
2	231	1200
2	265	1000
2	275	500
3	185	250
3	201	1000
3	189	1200
3	275	2000
3	254	3000
3	287	1200
3	298	1000

Thanks,

Hansel

[tulasik]

Hi, I am trying to solve a similar problem but with a small adition to the above data set. Lets say that the above dataset has a sales channel with values as online and offline and its possible that for a customer, previous month sales was offline and the current month is online. I need to calculate, count of customers by sales channel for the repeat customers. I have a different data set with similar problem and below is the link to the data and the pbix file with the model. Any help is highly appreciated.

https://1drv.ms/u/s!AgngHXmFWDwEaI_RCsG2MYbfATc?e=5Pxhk9

[LivioLanzo]

@hansel

you need to create two disconnected tables containing the unique months list

Then add the two silcers and these two measures:

New Customers = 
IF(
    HASONEVALUE( 'PreviousMonth'[PreviousMonth] ),
    IF(
        HASONEVALUE( ThisMonth[This Month] ),
        IF(
            SELECTEDVALUE( ThisMonth[This Month] ) > SELECTEDVALUE( 'PreviousMonth'[PreviousMonth] ),
            CONCATENATEX(
                EXCEPT(
                    CALCULATETABLE( VALUES( Data[Customer No] ), TREATAS( { SELECTEDVALUE( ThisMonth[This Month] ) }, Data[month] ) ),
                    CALCULATETABLE( VALUES( Data[Customer No] ), TREATAS( { SELECTEDVALUE( 'PreviousMonth'[PreviousMonth] ) }, Data[month] ) )
                ),
                [Customer No],
                " - "
            ),
            "This Month must be after previous Month"
        ),
    "Please select only one value for This Month"
    ),
    "Please Select only one value for previous month"
)

Lost Customers = 
IF(
    HASONEVALUE( 'PreviousMonth'[PreviousMonth] ),
    IF(
        HASONEVALUE( ThisMonth[This Month] ),
        IF(
            SELECTEDVALUE( ThisMonth[This Month] ) > SELECTEDVALUE( 'PreviousMonth'[PreviousMonth] ),
            CONCATENATEX(
                EXCEPT(
                    CALCULATETABLE( VALUES( Data[Customer No] ), TREATAS( { SELECTEDVALUE( 'PreviousMonth'[PreviousMonth] ) }, Data[month] ) ),
                    CALCULATETABLE( VALUES( Data[Customer No] ), TREATAS( { SELECTEDVALUE( ThisMonth[This Month] ) }, Data[month] ) )
                ),
                [Customer No],
                " - "
            ),
            "This Month must be after previous Month"
        ),
    "Please select only one value for This Month"
    ),
    "Please Select only one value for previous month"
)

[hansel]

Hi @LivioLanzo,

Thanks for the reply.

The solution works perfectly, but I need the Customer No. for furthur Drillthrough to show details of New/Lost Customer. So is there any other way to get results of comparison in listed format(to use in table visualization), from which I can right click on Customer No. and drillthrough to details.

[LivioLanzo]

Hello @hansel

Which details would you love to show? Do you have a customers dimension table?

Maybe you could share some more details ?

[hansel]

Hello @LivioLanzo,

I've shown only three columns of the original dataset in sample data, whereas my dataset has many more columns(username, address, contact information and other personal information).

In my report I'm having a customer detail drillthrough page with all available details about customer, which can be accessed by right clicking on Customer No. and then click drillthrough. So in any Visualization or chart in my report i can right click on Customer No. and drillthrough to Customer details page.

But in solution you have used the concatenate function and card visualization, which does not support drillthrough. 

I need to display the comparision results in list or tabular format, then i can select a single coustomer and drillthrough to the Customer details page.

Sorry, I can't share more column details as it contains personal infromation.

Thanks, 

Hansel

[LivioLanzo · Accepted solution]

@hansel

find the file here: https://1drv.ms/u/s!AiiWkkwHZChHjzAEtSDEpD6Qhbi-

[hansel]

Hi @LivioLanzo,

The file you shared is exactly what i needed, Thanks.

But when I implemented it in my report, it showed blank list as well as count of Lost and New Customers when I set the filter as Lost/New Customer is 1.

In your file it works perfectly fine with sample data.

In my report the Customer No are made up of numbers and letters, so the Customer No column is in text format (Does this has anything to do with blank results?)

Or is there anything I should do while implementing.

[LivioLanzo]

Hi @hansel

looks like a problem of relationships. What does your model looks like>?

[hansel]

Hi @LivioLanzo,

Here's my model.

[LivioLanzo]

@hansel

try to delete the relationship between sales and thismonth and between sales and previousmonth

[hansel]

Hi @LivioLanzo

it worked, I deactivated the relationship between sales and thismonth and between sales and previousmonth and changed the cross filter direction to both from manage relationships

Thanks a lot for your time and help 🙂

[LivioLanzo]

@hansel  well done !

[hansel]

@LivioLanzo

deleting relationship throws error (USERELATIONSHIP function can only use the two columns references participating in relationship) in Lost Customers and New Customers calculation

[LivioLanzo]

@hansel

I would need to see your file in order to know what is the problem but generally speaking if you follow the same approach as in my file you should be fine