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
cbeg
Frequent Visitor

Using a measure as if it was hardcoded for a multiplier

I may be totally off-base here in my approach, but here is my situation.

 

I have a measure (albeit, likely not a good measure) that gets me this correct value:

cbeg_0-1720642821135.png

Account Renewal Rate T3M = CALCULATE([% Accounts Renewed], FILTER(ACCOUNT_ANALYSIS, ACCOUNT_ANALYSIS[Trailing Months LRD]<=-1 && ACCOUNT_ANALYSIS[Trailing Months LRD] >=-3))

The goal was to get a measure not relying on visual filters to calculate the aggregate prior 3 months (April, May, June)'s renewal rate.

The Trailing Months LRD (Last Renewal Date) uses this workaround to identify the prior 3 full months; other calculations I tried were including July numbers, which is not correct for this instance.

I am using a date table, but it is connected to a different date field not relevant to this current situation.

 

Now, looking at future accounts and dates, which is a different date field, I want to use the Account Renewal Rate T3M value to multiple by these numbers in the table to get a projected rate of renewal:

cbeg_1-1720642865679.png

cbeg_4-1720644203355.png

 

In other words, 516 * 0.6310 = 326 accounts are projected to renew.

 

But I end up getting this, which is not correct, and obviously shows some blank rows:

cbeg_2-1720643338377.png

 

So I'm guessing this is a filtering (visual or measure) or date problem, as these tables are using two different date perspectives and filtering respectively. I simply want the value from my measure to use as a multiplier.

 

In a prior iteration, we just hardcoded the trailing 3 months value (e.g., Measure = 0.6310) but I'd like to make this dynamic so I don't have to update it each month.

 

Any thoughts? Thanks in advance!

 

2 REPLIES 2
Transform121
Frequent Visitor

if it's a different date field in a different table (but there's a relationship between these two tables) you may find that the filtering you're applying on one table is feeding through the relationship you have, causing a 'correct data' filtering with what you're trying to display but a wrong outcome for you.

 

Is it possible to count the open eligible accounts (or sum whichever formula)? 

 

It would be something like the following:

Expected Account Renewal = 

VAR __EligibleAccounts = CALCULATE(COUNT(Table1[ID]), FILTER (Table1, Table1[ID] = Table2[ID]))

RETURN
__EligibleAccounts * [Account Renewal Rate T3M]

You can create a custom column with the variable as well which you could use to show the values next to the measure. You should be able to replicate the [Open Eligible Accounts] column inside of your other table. The __EligibleAccounts would need to flex between count/sum/countrows depending on your data requirements.

 

This method would ensure that you're not being hindered by a relationship issue because you're consolidating the data into a singular table, and then you can explore changing your filtering options.

Thanks for your response! All of my data is within the same table 🙃 but this is giving me something to think about, maybe I need separate the data into their own tables (Past vs. Future, Renewed vs. Upcoming).

 

I did try a custom column that simply references the measure, but this didn't work. I'll keep trying some different methods.

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.

July Newsletter

Fabric Community Update - July 2024

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