topic Re: Trying to get trending revenue for clients active in a specific quarter in Desktop

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

kjohnanand — Fri, 05 Apr 2024 15:41:52 GMT

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

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

amitchandak — Fri, 05 Apr 2024 23:32:53 GMT

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

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

Ashish_Mathur — Sat, 06 Apr 2024 03:40:15 GMT

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.

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

Ashish_Mathur — Sat, 06 Apr 2024 04:45:52 GMT

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.

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

kjohnanand — Tue, 09 Apr 2024 19:46:53 GMT

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.

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

Ashish_Mathur — Wed, 10 Apr 2024 00:00:13 GMT

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

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

kjohnanand — Wed, 10 Apr 2024 00:29:52 GMT

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.

Clients Test

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

Ashish_Mathur — Wed, 10 Apr 2024 06:48:07 GMT

Check this revised file.

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

kjohnanand — Wed, 10 Apr 2024 13:21:42 GMT

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=msexcel

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

kjohnanand — Wed, 10 Apr 2024 20:19:13 GMT

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.

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

Ashish_Mathur — Thu, 11 Apr 2024 05:31:32 GMT

Access denied message.