cancel
Showing results for
Did you mean:
Community Champion

## Create a measure/calculated field

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

Proud to be a Super User!

1 ACCEPTED SOLUTION
Frequent Visitor

Hello,

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

Vicenç Lahoz

3 REPLIES 3
Frequent Visitor

Hello,

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

Vicenç Lahoz

Regular Visitor

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.

Super User

Not sure of your exact data layout without example data.

Have a look here:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors