Calculate Difference Between Dates in the same Column and the %

[Anonymous]

Hi

I am new to Power Bi, I have a problem and hope someone will be so kind to help:

I need to calculate a variance of the Sales (Column C) by Region (Column A) between the two years (Column B)

[v-chuncz-msft]

@Anonymous ,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Students-variations-year-over-year/m-p/345749#M155124

[Anonymous]

Hi

Thanks for your reply, but I don't see how this post helps me to resolve my DAX formula issue.

[Anonymous]

Sorry, clicked on the Post button by accident and don't know how to edit the post.

... and I also need to calculate the %.

Any help would be greatly appreciated.

[Gordonlilj]

Hi,

You could try the solution provided here and see if it works for you

How to create variance field and variance %

[Anonymous]

Hi

Thanks for the link, I used the formula below:

_Sales Variance = CALCULATE(SUM(Data[Sales_value £k]),FILTER(Data,'Data'[fiscal_year]=2018-19))-CALCULATE(SUM(Data[Sales_value £k]),FILTER(Data,'Data'[fiscal_year]=2017-18))

But an error message:

MdxScript(Modell) (10 80) Calculation error in measure 'Data'[_sales variance]:DAX comparison operations do not support comparing values of type text with values of type integer. Consider using the Value or Format funciton to convert one of the values.

Please Help, I am really struggling. 😞

[Gordonlilj]

Right, looking at the code the problem here is that the fiscal year is of the data type text because it contains a hyphen.

The easiest solution would be either replace your current fiscal year column or create a duplicate fiscal year column and than remove the hyphen and change the datatype to a whole number.

In the power query you could just duplicate the column, replace the hyphen with nothing and than convert it to whole number.

Or you could create a custom column and use the following dax code

Column = SUBSTITUTE(Table[YearColumn],"-","")

Just replace the table & column with your own and format it as a whole number under the modeling tab. Then use this new column in the code and it should work.

[Anonymous]

Hi

Thanks for your reply. The variance DAX works now, but Variance % is not:

Sales_Var% = DIVIDE('Data'[_Sales Variance],CALCULATE(sum(Data[Sales_value £k]),FILTER(ALL(Data[FiscalYearNew]=201718)))

Too few arguments were passed to the filter function. The mimimum argument count for the function is 2.

Any help is greatly appreciated.

[Anonymous]

Spoke too soon, the variance doesn't work, the value in the variance column  is exactly the same as the Sales column.

Please help!!!