cancel
Showing results for
Did you mean:
Helper I

## Calculated Column with Dummy (1/0) for revenue growth

Hi everyone,

I am currently working on a dashboard in which we aggregate the revenue data of our clients to provide them with some market analyses. We have 17 companies that send us their monthly revenue data, I put the data in a database and I run them through PowerBI to make the report. I have included a table to show how the main data table looks like, the Date Column is linked to a separate Date table:

 Company Statement Date Value Company 1 Revenue 1 january 2021 800 Company 2 Revenue 1 january 2021 900 Company 3 Revenue 1 january 2021 750 Company 1 Revenue 1 december 2020 700 Company 2 Revenue 1 december 2020 890 Company 3 Revenue 1 december 2020 675 Company 1 Revenue 1 november 2020 965 Company 2 Revenue 1 november 2020 678 Company 3 Revenue 1 november 2020 598 ... ... ... ...

I wanted to create a Calculated Column DummyGrowth with a Dummy variable that shows a 1 if the revenue (Value) for a specific company for a specific month is higher than the revenue of the same month in the year before, and a 0 if it is lower. For example, we would compare the value for Company 1 on 1 january 2021 with the value for Company 1 on 1 january 2020.

After that, I would create a measure GrowthPercentage = AVERAGE(DummyGrowth) and display it per month, effectively giving me the percentage of the companies that had a growth in their revenue for that month.

I am not able to figure out how to get the initial Calculated Dummy Column there. Can someone share with me a DAX formula that would make this possible? If this is not possible, is there another workaround to get this percentage?

Thank you very much!!

1 ACCEPTED SOLUTION
Community Champion

Hi @SDR3078 - try this for your Growth column

``````Growth =
var __ThisMth = Revenue[Date]
var __ThisCo = Revenue[Company]
var __ThisStmt = Revenue[Statement]
var __PrevMth = DATEADD(Revenue[Date], -1, MONTH)
var __ThisValue = Revenue[Value]
var __PrevValue = LOOKUPVALUE(Revenue[Value],
Revenue[Company], __ThisCo,
Revenue[Statement], __ThisStmt,
Revenue[Date], __PrevMth)
RETURN
IF ( __ThisValue > __PrevValue, 1, 0)``````

Hope this helps

David

2 REPLIES 2
Community Champion

Hi @SDR3078 - try this for your Growth column

``````Growth =
var __ThisMth = Revenue[Date]
var __ThisCo = Revenue[Company]
var __ThisStmt = Revenue[Statement]
var __PrevMth = DATEADD(Revenue[Date], -1, MONTH)
var __ThisValue = Revenue[Value]
var __PrevValue = LOOKUPVALUE(Revenue[Value],
Revenue[Company], __ThisCo,
Revenue[Statement], __ThisStmt,
Revenue[Date], __PrevMth)
RETURN
IF ( __ThisValue > __PrevValue, 1, 0)``````

Hope this helps

David

Helper I

Thanks @dedelman_clng , you saved me, this did exactly what I wanted 😁😁😁

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors