Calculate Difference Between Dates in the same Column and the %

Question

&nbsp;&nbsp;HiI am new to Power Bi, I have a problem and hope someone will be so kind to help:I need to calculate a variance&nbsp;of the Sales (Column C) by&nbsp;Region&nbsp;(Column A)&nbsp;between the&nbsp;two years (Column B)&nbsp;

anonymous · Answer

Sorry, clicked on the Post button by accident and don't know how to edit the post.&nbsp;... and I also need to calculate the %.&nbsp;Any help would be greatly appreciated.

gordonlilj · Answer

Hi,&nbsp;You could try the solution provided here and see if it works for youHow to create variance field and variance %&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;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))&nbsp;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.&nbsp;Please Help, I am really struggling. :(&nbsp;

gordonlilj · Answer

Right, looking at the code the problem here is that the fiscal year is of the data type text because it contains a hyphen.&nbsp;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.&nbsp;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 codeColumn = SUBSTITUTE(Table[YearColumn],"-","")Just replace the table &amp; 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.&nbsp;

anonymous · Answer

HiThanks 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)))&nbsp;Too few arguments were passed to the filter function. The mimimum argument count for the function is 2.&nbsp;Any help is greatly appreciated.&nbsp;

Forum Discussion

Calculate Difference Between Dates in the same Column and the %

8 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Calculating Differences

Calculating day/time difference

How to add two different calculated columns of two different table