Forum Discussion
Calculate Difference Between Dates in the same Column and the %
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.
Solution SageHi,
You could try the solution provided here and see if it works for you
How to create variance field and variance %
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.
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.