Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hansel
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

@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!  

View solution in original post

14 REPLIES 14
tulasik
Frequent Visitor

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
Solution Sage
Solution Sage

@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!  

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.

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!  

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

@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!  

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.

 

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!  

Hi @LivioLanzo,

 

Here's my model.

Screenshot (39).png

@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!  

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 🙂

@hansel  well done !

 


 


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


Proud to be a Datanaut!  

@LivioLanzo

 

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

@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

 

 

 


 


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


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.