cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

 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

1 ACCEPTED SOLUTION
Solution Sage

@hansel

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

Proud to be a Datanaut!

14 REPLIES 14
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

Solution Sage

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

Proud to be a Datanaut!

Frequent Visitor

Hi @LivioLanzo,

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.

Solution Sage

Hello @hansel

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

Maybe you could share some more details ?

Proud to be a Datanaut!

Frequent Visitor

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

Solution Sage

@hansel

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

Proud to be a Datanaut!

Frequent Visitor

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.

Solution Sage

Hi @hansel

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

Proud to be a Datanaut!

Frequent Visitor

Hi @LivioLanzo,

Here's my model.

Solution Sage

@hansel

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

Proud to be a Datanaut!

Frequent Visitor

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 🙂

Solution Sage

@hansel  well done !

Proud to be a Datanaut!

Frequent Visitor

@LivioLanzo

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

Solution Sage

@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

Proud to be a Datanaut!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors