Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to 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
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
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |