I have a data source that can only be downloaded YTD. I want to show a comparison difference between them.
My data looks like this:
The matrix table I am designing is to look like this. I am trying to add the variance column which is the difference between the columns added on Power BI. Can a DAX resolve this?
Suppose this is your original dataset with add column for month (I am just giving some random value rather based on your value):
In order to produce the expected Matrix table, first you need to create categories, such as sale, cost, GP (I only create a sample, but not a complete list)
Table 2 = {"Sale", "Cost", "GP"}
Next, create a new column for the Jun value based on each category:
June = IF('Table 2'[Categories]="Sale",CALCULATE(SUM(Vendor[Sale]),
FILTER(Vendor,Vendor[Header]="Jun")),
IF('Table 2'[Categories]="Cost",CALCULATE(SUM(Vendor[Cost]),
FILTER(Vendor,Vendor[Header]="Jun")),
IF('Table 2'[Categories]="GP",CALCULATE(SUM(Vendor[GP]),
FILTER(Vendor,Vendor[Header]="Jun")),0)))
Followed by July with the same logic:
July = IF('Table 2'[Categories]="Sale",CALCULATE(SUM(Vendor[Sale]),
FILTER(Vendor,Vendor[Header]="Jul")),
IF('Table 2'[Categories]="Cost",CALCULATE(SUM(Vendor[Cost]),
FILTER(Vendor,Vendor[Header]="Jul")),
IF('Table 2'[Categories]="GP",CALCULATE(SUM(Vendor[GP]),
FILTER(Vendor,Vendor[Header]="Jul")),0)))
Then calculate Delta based on the new column create:
Delta = CALCULATE(SUM('Table 2'[July]),
FILTER('Table 2','Table 2'[Categories]=EARLIER('Table 2'[Categories]))) -
CALCULATE(SUM('Table 2'[June]),
FILTER('Table 2','Table 2'[Categories]=EARLIER('Table 2'[Categories])))
Here is the completed new table (Table 2):
Finally, you can create the expected Matrix table using all the columns from the Table 2 as follows (the sequence can be solved by index; I won't settle here).
