cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SDR3078
Helper I
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:

 

CompanyStatementDateValue
Company 1Revenue1 january 2021800
Company 2Revenue1 january 2021900
Company 3Revenue1 january 2021750
Company 1Revenue1 december 2020700
Company 2Revenue1 december 2020890
Company 3Revenue1 december 2020675
Company 1Revenue1 november 2020965
Company 2Revenue1 november 2020678
Company 3Revenue1 november 2020598
............

 

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
dedelman_clng
Community Champion
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

View solution in original post

2 REPLIES 2
dedelman_clng
Community Champion
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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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