Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors