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
Joseph_Hchaime
Helper III
Helper III

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

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: 

Joseph_Hchaime_0-1661238128466.png

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

EylesIT_0-1661265095996.png

 

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.

EylesIT_1-1661265281317.png

 

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

EylesIT_0-1661271787978.png

 

Hope it helps!

 

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

@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
Helper III
Helper III

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. 
 
Joseph_Hchaime_0-1661255946266.png

 

Anonymous
Not applicable

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

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
Not applicable

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

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
Not applicable

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

EylesIT_0-1661251516281.png

The table visual can be changed to a chart visual.

Is this what you are after?

 

 

Joseph_Hchaime_0-1661252986044.png

Here is what I got

Anonymous
Not applicable

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

EylesIT_0-1661257008487.png

 

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.

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
Not applicable

@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

EylesIT_0-1661257961484.png

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.

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
Not applicable

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

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

 

Joseph_Hchaime_0-1661260397933.pngJoseph_Hchaime_1-1661260452626.png

Joseph_Hchaime_2-1661260479263.png

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
Not applicable

@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
Not applicable

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

EylesIT_0-1661265095996.png

 

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.

EylesIT_1-1661265281317.png

 

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

EylesIT_0-1661271787978.png

 

Hope it helps!

 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.