How to get a field from one table to another table

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi,

I can only get Net profit (not operating profit).  Download the PBI file from here.

Hope this helps.

 

[Anonymous]

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.

[Ashish_Mathur]

That is simply because there is no row for operating profit in your data.

[v-juanli-msft]

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

[Anonymous]

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

[v-juanli-msft]

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.

[v-juanli-msft]

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

Please note:

this year should be year(today()), since your data doesn't have today's data, so i modify this as above.

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.

[rajulshah]

Hello @Anonymous,

You can write below measure instead if SUM:

CALCULATE(SUM('Actual - General Ledger'[Revenue]),SAMEPERIODLASTYEAR(Dates[Date]))

Hope this helps.

[Anonymous]

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.