topic Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients in DAX Commands and Tips

Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 07:07:18 GMT

Hi I need to build two separate visuals and I need help with creating the measures for said visuals.

1) Visual 1: Client Growth Rate.

The formula is:

instead of Q1/Q2/Q3/Q4 I want to use Years.

Available Columns: Net Billed, Bill Issue Date (dd/mm/yyyy). Obviously the legend part is the easiest part.

However, I'm not able to achieve the CGR per year. Can someone help please?

Visual 2: I need to check for clients with no "activities" within the last 6 months dynamically and count them as "dormant clients"

I have a table with activities and dates of each activity but it does not show zeros (i.e. clients with no activities).

I have a separate clients table. My theory is I can reference ALL the clients from the clients table and filter out those with no activities within the last 6 months but I'm not sure how to do it.

Please help!!!

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 09:29:14 GMT

Joseph_Hchaime , try this measure

CGR % = VAR vThisYear = SELECTEDVALUE('dimDate'[Year]) VAR vLastYear = vThisYear - 1 VAR vCCRThisYear = CALCULATE( SUM('Table'[Net Billed]), ALL('dimDate'), 'dimDate'[Year] = vThisYear ) VAR vCCRLastYear = CALCULATE( SUM('Table'[Net Billed]), ALL('dimDate'), 'dimDate'[Year] = vLastYear ) VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear) RETURN vCGR

For visual 2, what exactly do you mean when you say you need to "check for clients with no activities"? Does the visual need to list the clients with no activities? Or should it show the number of clients with no activities? How should the time period be selected? Should any time periods be displayed in the visual?

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 10:06:22 GMT

Hi,

The measure you provided didn't work. blank result. even on its own and I didn't get any noticeable errors.

For the 2nd visual i want # of Clients with 0 "activities" in the past 6 months

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 10:16:02 GMT

Joseph_Hchaime , ok, you will need to provide more information for this forum to be able to help you.

What is the name of the table and field that you are using (or intend to use) for the X-axis on your chart visual?

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 10:26:45 GMT

VW_Bills_Table (it's an SQL view that I'm directquerying) column name is Bills_Issue_Date (it's formatted as dd/mm/yyyy) but I can create bins for years right?

Revenue is a Measure in another "Measures Table". It's not a table/column, it's a calculated Measure called [Net Billed in USD].

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 10:49:38 GMT

Joseph_Hchaime , this is what I have come up with.

Sample data

Bills_Issue_Date Net Billed

01 January 2020	1000
01 July 2020	1500
01 January 2021	2000
01 July 2021	777
01 January 2022	1234
01 July 2022	2345

[Net Billed in USD] measure:

Net Billed in USD = SUM('VW_Bills_Table'[Net Billed])

[CGR %] measure (format:Percentage)

CGR % = VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bills_Issue_Date].[Year]) VAR vLastYear = vThisYear - 1 VAR vCCRThisYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bills_Issue_Date].[Year] = vThisYear ) VAR vCCRLastYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bills_Issue_Date].[Year] = vLastYear ) VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear) RETURN vCGR

Place a table visual on a report.

Add Year from the [Bills_Issue_Date] hierarchy into the table visual.

Add [Net Billed in USD] measure onto the table visual.

Add [CGR %] measure onto the tablew visual.

This gives me this result:

The table visual can be changed to a chart visual.

Is this what you are after?

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 11:09:59 GMT

Here is what I got

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 11:59:13 GMT

I even tried this variation of the statement:

CGR % =

VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bill_Issue_Date Years])

VAR vLastYear = vThisYear - 1

VAR vCCRThisYear =

CALCULATE( SUMX('Basic Measures',

[Net Billed in USD]),

ALL('VW_Bills_Table'),

'VW_Bills_Table'[Bill_Issue_Date Years] = vThisYear

)

VAR vCCRLastYear =

CALCULATE(SUMX('Basic Measures',

[Net Billed in USD]),

ALL('VW_Bills_Table'),

'VW_Bills_Table'[Bill_Issue_Date Years] = vLastYear

)

VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear)

RETURN vCGR

it didn't work.

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 12:18:39 GMT

Joseph_Hchaime , that's odd. What items do you have in the Values section of your Table visual? This is what I have:

I notice in your measure you have

VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bills_Issue_Date])

instead of

VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bills_Issue_Date].[Year])

It needs to get the year, not the date.

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 12:21:33 GMT

it won't let me. I get an error when i add .[Year] even though the column is formatted as Date but with no hierarchy

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 12:34:29 GMT

Joseph_Hchaime , ok, make therse changes:

Create a Calculated Column in Power BI for your table VW_Bills_Table

Bills_Issue_Date_Year = YEAR(VW_Bills_Table[Bills_Issue_Date])

Add the Bills_Issue_Date_Year column to the Values section of your Table visual, and make sure to set the column to Don't Summarize

Change the measure [CGR %] to use the new column Bills_Issue_date_Year

CGR % = VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bills_Issue_Date_Year]) VAR vLastYear = vThisYear - 1 VAR vCCRThisYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bills_Issue_Date_Year] = vThisYear ) VAR vCCRLastYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bills_Issue_Date_Year] = vLastYear ) VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear) RETURN vCGR

That should do it.

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 13:00:55 GMT

Tried that. still not working. I can't use calculated columns as this will change my schema when i publish on the service and the gateway won't be able to refresh the data from the cloud sql

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 13:05:06 GMT

Joseph_Hchaime , what do you mean it is not working? Please give details about what is going wrong.

If you do not have a column for [Year] in your data model, how do you intend use [Year] as an X-axis on your chart visual?

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Joseph_Hchaime — Tue, 23 Aug 2022 13:15:19 GMT

I tried creating "Bins" based on the bill_issue_date column in Years. I changed the DAX statement you sent me to reference the "bin" column instead of the bill_issue_date. It didn't work. it still gave me a blank result.

Screenshots below

Dax Code:

CGR % =

VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bill_Issue_Date_Year])

VAR vLastYear = vThisYear - 1

VAR vCCRThisYear =

CALCULATE( SUMX('Basic Measures',

[Net Billed in USD]),

ALL('VW_Bills_Table'),

'VW_Bills_Table'[Bill_Issue_Date_Year] = vThisYear

)

VAR vCCRLastYear =

CALCULATE(SUMX('Basic Measures',

[Net Billed in USD]),

ALL('VW_Bills_Table'),

'VW_Bills_Table'[Bill_Issue_Date_Year] = vLastYear

)

VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear)

RETURN vCGR

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 14:56:40 GMT

Joseph_Hchaime , I have no idea what you are referring to when you said you have created "bins". There is no need to do that and I have not suggested that. If you are doing something weird with "bins" in conjunction with my suggested solution then I cannot help you, sorry.

[Edit: I haven't used Grouping/bins before in Power BI. I have now looked into it and amended my suggested solution using Grouping/bins. Please see my more recent post]

In the measure you posted above, you are using a SUMX. You do not need to use a SUMX. Please use the measure I posted in my suggested solution.

To help me see what might be going wrong, please create these two new measures, place them on the table visual, and post the results.

NetBilledThisYear = VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bill_Issue_Date_Year]) RETURN CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bill_Issue_Date_Year] = vThisYear )

and

NetBilledLastYear = VAR vThisYear = SELECTEDVALUE('VW_Bills_Table'[Bill_Issue_Date_Year]) VAR vLastYear = vThisYear - 1 RETURN CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), 'VW_Bills_Table'[Bill_Issue_Date_Year] = vLastYear )

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Tue, 23 Aug 2022 16:23:15 GMT

Joseph_Hchaime , ok I have got it working with Power BI's Grouping/binning functionality.

I created the group called Bills_Issue_Date_Years like this:

Add the Bins_Issue_Date_Years onto the table visual.

Then change the measure [CGR %] to this:

CGR % = VAR vThisYear = YEAR(MAX('VW_Bills_Table'[Bills_Issue_Date_Years])) VAR vLastYear = vThisYear - 1 VAR vCCRThisYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), YEAR('VW_Bills_Table'[Bills_Issue_Date_Years]) = vThisYear ) VAR vCCRLastYear = CALCULATE( [Net Billed in USD], ALL('VW_Bills_Table'), YEAR('VW_Bills_Table'[Bills_Issue_Date_Years]) = vLastYear ) VAR vCGR = DIVIDE((vCCRThisYear - vCCRLastYear), vCCRLastYear) RETURN vCGR

This gives me the same results that I got using a Calculated Column.

And using yoour data, these are the results I get:

Hope it helps!

Re: Two DAX formulas into visuals: 1)Client Growth Rate 2) Dormant Clients

Anonymous — Thu, 25 Aug 2022 07:09:10 GMT

Joseph_Hchaime , thanks for accepting my solution for "Visual 1: Client Growth Rate".

Could I suggest that you post your second question "Visual 2: I need to check for clients with no "activities" within the last 6 months" as a separate post, so that it can be answered and solved separately? A separate post will keep the two problems separate and make it easier for other members to find that are searching for how to solve a similar problem.