Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |