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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kjohnanand
Helper I
Helper I

Trying to get trending revenue for clients active in a specific quarter

I have a dataset that essentially organizes clients by active/inactive status by quarter, along with their rolling 12 revenue. Here's what I'm trying to do with it. Let's say there are 993 active clients in Q1 2020. If I select Q1 2020, I want to see revenue for those 993 clients in a trending graph through 2020, 2021, 2022, and 2023, regardless of whether they were active in any other quarter. Essentially, the filter would create a static active client list for that quarter and show me the total revenue of all those clients moving forward. I've tried to create a second date table, but I'm not really sure how to proceed with this issue.

I've attached sample data below.

 

Sample Data 

1 ACCEPTED SOLUTION

Check this revised file.

Ashish_Mathur_0-1712731657756.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Your question is something similar to this one - Calculate how many customer in a selected range ge... - Microsoft Fabric Community.  Please study this post carefully.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello, thank you for the reply. I tried this out and I believe I am close, but I'm not sure how to use this function to filter for active clients. When I try to form the table like in the basket sample, I'm getting duplicate values in the columns. Here is the function I am using. 

 

Rev = CALCULATE(SUM('RCB Core Revenue'[Revenue]), CALCULATETABLE(SUMMARIZE(VALUES('RCB Core Revenue'[Entity ID]), 'RCB Core Revenue'[Entity ID]), ALL('_Calendar_Table'), USERELATIONSHIP('RCB Core Revenue'[Date], _Calendar_Table2[Date])))

 

The quarterly numbers seem to be correct when not filtering for active clients.Screenshot 2024-04-09 154545.png

What results do you get when you create a slicer and select Active in the slicer?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I added active to the measure like this.

Rev = CALCULATE(SUM('RCB Core Revenue'[Revenue]), 'RCB Core Revenue'[Active vs Deconverted] = "Active", CALCULATETABLE(SUMMARIZE(VALUES('RCB Core Revenue'[Entity ID]), 'RCB Core Revenue'[Entity ID]), ALL('_Calendar_Table'), USERELATIONSHIP('RCB Core Revenue'[Date], _Calendar_Table2[Date])))

 The issue however is that all the column years are the same, which I believe should not be the case. Based on the year and quarter I select, it should populate with the revenue of clients that were active in the select quarter, which should be different for every quarter. I thyink something is messed up in my calculation. I have attached a test PBIX I'm working with. Let me know if that works.

Screenshot 2024-04-09 202303.png

Clients Test 

Check this revised file.

Ashish_Mathur_0-1712731657756.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Update - I made a new formula using the same calculation, but to calculate client count instead, and all of those counts are what they should be. Because of that, I think the issue is due to the "revenue from active" measure. I believe something about that is messing up the revenue calculation to make it not match, but I'm not sure what it could be.

Hey Ashish. I double checked the numbers and while we're close, I don't think there is an exact match. For example, when filtering to clients active in Q2 2020, I should be seeing ~2.65B in Q4 2023 revenue, but in the bar graph I'm seeing ~2.58B. I think something is being filtered out that shouldn't be. I've attached my check file (should be the same as the input file).

 

https://docs.google.com/file/d/1MPF7LNlam_Xq07j_BOOEj-ZepQMCT1WM/edit?usp=docslist_api&filetype=msex...

Access denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

I suggest you share a smaller dataset (with only the required columns) of only a few clients.  On that abridged dataset, show the expected result in a simple Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@kjohnanand , Create an independent table to select quarter

 

then try a measure like

 

Measure=

var _tab = summarize(Filter(Table, Table[quarter] in values(quarter[quarter]) ), Table[client])

return

calculate(sum(Table[Revenue]), filter(Table, Table[client] in _tab ) )

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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