Creating Dynamic Revenue Range

[Anonymous]

I'm attempting to create a measure so I can appropriately bucket companies into $ ranges:

$1 - $5,000

$5,000 - $10,000

$10,000 - $20,000

Over $20,000

But the tricky part is doing it based on a date slicer, so depending on the date selection companies could move between categories. I currently have a table that looks like this:

 

So I have a date slicer that is currently set to 12/18/2017-12/18-2018 and the $ range I'm currently looking at is $10,000-$20,000. My 'Dynamic Revenue' formula is taking invoice dates into account and ensuring they fall within my date range I've selected:

 

My 'Revenue' column is just the grand total  for each company regardless of dates selected.

My ' $ Range' formula looks like this (which isn't working properly):

 

 My aim is taking this info:

 

And bucketing the companies into the appropriate categories. So if the above range is looking at dates of 12/18/2017-12/18/2018, I would need some kind of measure/column that is placing these companies into a category of $1-$5,000 since that is what they made between those dates. The below screenshot is determining their $ range based on their grand total which isn't what I'm looking for:

I'm trying to give end users the ability to select any date range they'd like, along with a slicer of the $ Ranges so they can see how many companies made X amount of money between X dates.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Sorry for the delay.

If I understand your scenario correctly that you want to create a date slicer and a range slicer to filter the companys which belongs to the range during the period.

It seems that you want to calculate the dynamic Revenue, that should be a measure.

However, as we know that we cannot use measure as the slicer. So I'm afraid we cannot create the Dynamic Revenue Range to achieve your requirement.

Best  Regards,

Cherry

[AlB]

Hi  @Anonymous

It would help if you post the code and data in text format so that it can be readily copied or otherwise share the pbix.

Check this out. It might come in handy

[Anonymous]

@AlB my pbix file is connected to our SQL database I will try to keep it simpler by looking at 1 company. So on my revenue table is this:

 

So this company from May 2014 - Today has generated a grand total of $25,203.43. So if I were to bucket this company into one of the category ranges, it would obviously be in the $20,000+ range.

Now let's say I want to see how much money this company made from May 2016-Today. That would look like this:

So now the total is $15,996.45. So now when looking at this, I would want this company to now be bucketed in the $10,000-$20,000 range since that is what they made between May 2016-Today. Is making a measure like this possible? I have my revenue table connected through a date table through an inactive relationship (Invoice Date->Date) :

So if a user selects a time period of one year (Date table used as slicer) for instance, and let's say they choose the $10,000-$20,000 range. They would be looking at all companies in that one year period that had a total revenue of $10,000-$20,000, despite if their overall grand total (ignoring all dates) is over $20,000.