Forum Discussion

hansel's avatar
hansel
Frequent Visitor
7 years ago
Solved

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

 

 

14 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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's avatar
      hansel
      Frequent Visitor
      7 years ago

      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's avatar
        LivioLanzo
        Icon for Solution Sage rankSolution Sage
        7 years ago

        Hello hansel

         

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

         

        Maybe you could share some more details ?

  • tulasik's avatar
    tulasik
    Frequent Visitor
    5 years ago

    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