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,
I have situation to display column data in row format. For that I have created new table and used Switch statement and got the results. Now for the same result, I want to display the value of Previous year. Now I am unable to get the year field to new table and write a condition to get previous year value.
My first table consists of all fields, second table has only attribute and index fields. How can I write a year condition in the same formula?
Regards,
Ajay Emmadi
Hi,
There should be no need to write such a lengthy formula to get the current year's value. Share data in an Excel file and show the expected output.
Hello @Ashish_Mathur ,
Please check the data sheet attached in the link.
https://drive.google.com/file/d/18W6FVyU2as05MXOlJZcRy54zaliX4jmP/view
I am looking for an ouptput as per the below format.
Here Op.Profit and Net Profit are calculated from the above values. For getting the values in row format I have used other table and created a measure as discussed earlier. Please check and help me with some easy DAX formula's.
Regards,
Ajay Emmadi
Hi,
I can only get Net profit (not operating profit). Download the PBI file from here.
Hope this helps.
Hello @Ashish_Mathur
What would be the challenge to get Op. Profit ?
For that reason I have created a new table and written such a lengthy formula. But the problem is number of columns are increasing in my aproach. To get current year amount I have created 8 new amount columns and converted them to rows again. To get Previous year amount again i have to create 8 new columns which will affect the performance. I am looking for a right solution in doing it.
That is simply because there is no row for operating profit in your data.
Hi @Anonymous
How about my method?
There is no rows like Op. Profit or Net Profit in your data example.
Would you like to create another column to classify part 1 as Net Profit and part 2 as Op. Profit ?
Best Regards
Maggie
@v-juanli-msft ,
I have tried your aproach already but there should not be an expansion method in my aproach. It should look like a table.
Hi @Anonymous
In Row Header, turn off +/- icon option.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Create measures and add columns in a matrix.
this year =
VAR this_year =
YEAR ( TODAY () ) - 2
RETURN
CALCULATE (
SUM ( Sheet1[Amount] ),
FILTER ( Sheet1, Sheet1[Year] = this_year )
)
last year =
VAR this_year =
YEAR ( TODAY () ) - 2
RETURN
CALCULATE (
SUM ( Sheet1[Amount] ),
FILTER ( Sheet1, Sheet1[Year] = this_year - 1 )
)
Hello @Anonymous,
You can write below measure instead if SUM:
CALCULATE(SUM('Actual - General Ledger'[Revenue]),SAMEPERIODLASTYEAR(Dates[Date]))
Hope this helps.
Hi @rajulshah ,
We don't have date field in our dataset. We are using Year and Month. Also, I want to take the year and month field to new table without using any joins. So, your solution may not work for my issue.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |