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

[Joseph_Hchaime]

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

[Anonymous]

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

[Joseph_Hchaime]

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. 

[Anonymous]

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

[Joseph_Hchaime]

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 

[Anonymous]

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

[Joseph_Hchaime]

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

[Anonymous]

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

[Joseph_Hchaime]

Here is what I got

[Anonymous]

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

[Joseph_Hchaime]

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 

[Anonymous]

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

[Joseph_Hchaime]

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 

[Anonymous]

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

[Joseph_Hchaime]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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