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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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 ) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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