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

[kjohnanand]

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 

[Ashish_Mathur]

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.

[kjohnanand]

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.

[Ashish_Mathur]

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

[kjohnanand]

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 

[Ashish_Mathur · Accepted solution]

Check this revised file.

[kjohnanand]

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.

[kjohnanand]

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

[Ashish_Mathur]

Access denied message.

[Ashish_Mathur]

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.

[amitchandak]

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