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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors