cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hansel Frequent Visitor
Frequent Visitor

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

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

monthCustomer NoAmt Paid
1101500
11451000
11231200
1185250
11462000
1126500
1189500
1147500
2101500
2145500
2185500
21891000
22011200
22311200
22651000
2275500
3185250
32011000
31891200
32752000
32543000
32871200
32981000

 

Thanks,

Hansel

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

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

@hansel

 

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

13 REPLIES 13
LivioLanzo Super Contributor
Super Contributor

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

@hansel

 

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

 

Capture.PNG

 

 

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"
)

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

hansel Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

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

Hello @hansel

 

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

 

Maybe you could share some more details ?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

hansel Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

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

@hansel

 

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

hansel Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

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

Hi @hansel

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
hansel Frequent Visitor
Frequent Visitor

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

Hi @LivioLanzo,

 

Here's my model.

Screenshot (39).png

LivioLanzo Super Contributor
Super Contributor

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

@hansel

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 250 members 2,796 guests
Please welcome our newest community members: