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

[Anonymous]

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

[dedelman_clng · Accepted solution]

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]

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