Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kcantor
Community Champion
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
vlahoz
Frequent Visitor

Hello,

 

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

 

Vicenç Lahoz

View solution in original post

3 REPLIES 3
vlahoz
Frequent Visitor

Hello,

 

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

 

Vicenç Lahoz

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
Super User
Super User

Not sure of your exact data layout without example data.

 

Have a look here:

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors