Hello,
I am relatively new to DAX woudl like to know if this is possible what i am trying to do .
i have three table
1. transaction table for last 24 months named as facttable
has four columns 1. account 2. Amount 3. Actual 4.Month
2. Second table is dimaccountcode table having 1. account and 2. Nature column
3. Table is the date table
i have created a relation between facttable [account] and dimaccountcode[account]
and between calendar table date and facttable [month] column.
The nature column in the dimaccountcode table has
sales , cogs , and discount fields which i have pulled it to fact table using related function
Now i have created the following pivot table
| Year | 2023 | |
| BRANDDESC | All | |
| | | |
| Row Labels | Prv. Year Value | Crnt Year Value |
| January | | |
| Sales | - 22,721,371 | - 21,655,006 |
| Discount | 1,303,606 | 1,046,479 |
| Advt. & Promo | 2,801,615 | 2,638,647 |
| Cost of Goods Sold | 15,872,109 | 13,727,417 |
| Total | - 2,744,040 | - 4,242,463 |
| February | | |
| Sales | - 23,512,275 | - 23,789,792 |
| Discount | 1,239,852 | 1,291,257 |
| Advt. & Promo | 2,477,935 | 3,715,936 |
| Cost of Goods Sold | 16,717,937 | 15,362,707 |
| Total | - 3,076,551 | - 3,419,892 |
i need to calculate and add Gross profit as a measure but i am not able to. Gross profit is total of all the nature displayed in the above table . currently in the pivot field values column Prv. Year Value and Crnt Year Value has been selected this two measures have been calculated as follows.
Crnt Year Value:=sum(facttable[Amount])
Prv Year Value:=CALCULATE([Sum of Amount],SAMEPERIODLASTYEAR(dCalendar[Date])).
Any suggestion in assisting me to arriving at gross profit would be really helpful.
Thank you
