Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate Difference Between Dates in the same Column and the %

    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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Gordonlilj
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      Please help!!!