Create a measure/calculated field

[kcantor]

Greetings,

As a new PowerBI user, I am struggling wtih what is probably a simple DAX statement. Of course I have not successfully integrated this into PowerPivot either so all helap is appreciated.

In my current data, I have a field called TotalSales. When using this in the pivot table or in BI, I use the sum of total sales as the value and order year as columns. Rows vary by the type of information I am seeking. I use order week, category, and manufacturer as some examples for row lables.

What I need is a DAX formula to calculate a $variance and a % variance. In a pivot table, I can simply work outside the table and calculate the difference. IE: 2015Total Sales - 2014 Totals Sales, using cel references. This would return the $Variance. For the % variance, I use an IF statement: =IF(C7=0,"0",((D7-C7)/C7)) with C referencing 2014 sales and D referencing 2015 sales.

I am looking for a course in DAX but need a quick fix for this question. Can anyone here help me come up with a statement for these that would work in both tables?

Relevant Information:

Table Name: SalesPerformance Table

SalesTotals

OrderYear

Kind regards,

KCantor

[Anonymous · Accepted solution]

Hello,

Create a measure with CALCULATE(SUM([Sales Totals]), SAMEPERIODLASTYEAR([DateColumn])), and then, calculate the variance and %.

Vicenç Lahoz

[ngottier]

Is there any way to accomplish ^ when in direct query mode? Currently Power BI does not support PREVIOUSXXXXX, or LASTXXXX time look backs in direct query.

I would switch to import, but the data set is millions of rows and not really feasible to be an import.

[Greg_Deckler]

Not sure of your exact data layout without example data.

Have a look here:

http://www.daxpatterns.com/time-patterns/