cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McNulty Frequent Visitor
Frequent Visitor

Compare previous and new sales

 

Building a report and slicing by year and month.

I want to compare the sales with earlier sales, so I entered

 

sales_before = calculate(sum('Table'[Sales]), PreviousMonth('Table'[Date]))

 

however, nothing gets returned. Do I have to add a new column with a reformatted date?

 

Are there other simple ways to compare sales of now with sales in previous periods of time over flexible time periods? E.g. compare the last two weeks with the same two weeks in november 2014?

1 ACCEPTED SOLUTION

Accepted Solutions
piyush0308 Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

Try this one for Previous Month Sales. For me this one always works.

sales_before = calculate(sum('Table'[Sales]), PARALLELPERIOD('Table'[Date],-1,MONTH))

View solution in original post

11 REPLIES 11
konstantinos Senior Member
Senior Member

Re: Compare previous and new sales

Everything is possible with DAX but first for your measure to work you need a new Date Table and then have an active relantionship to the sales table. Up to now you cannot use DAX time intelligence funtions without date table.

 

There a few ways to create it. Import from excel or DB , create one with PowerQuery or create with DAX calculate table.

 PQ: http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/ 

DAX table: http://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/m-p/8967#M16...

 

Konstantinos Ioannou
McNulty Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

Thank you, Konstantinos, for helping me out.

However, I haven't been able yet to use calendar functions.

 

I created the DateTable with one column ([Date]) and a relationship between that column with the date-column in my other table, however, no results are shown if I try to use a function like PreviousMonth.

 

What am I missing?

 

 

piyush0308 Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

Try this one for Previous Month Sales. For me this one always works.

sales_before = calculate(sum('Table'[Sales]), PARALLELPERIOD('Table'[Date],-1,MONTH))

View solution in original post

McNulty Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

 

Thanks piyush, that works - and without creating an extra DateTable 🙂

 

The other formula also works when slicing by date from the DateTable (but not if I slice by date from the fact table).

leonardmurphy Established Member
Established Member

Re: Compare previous and new sales

Generally when you have a date table (or any lookup table), you want to hide the lookup values in the fact table and use the values in the lookup table exclusively.

 

For example, if you have two fact tables linked to a lookup table, and then filter using values from one of the fact tables rather than the lookup, the other fact table won't be filtered.

 

I aim to hide every column in my fact tables (leaving only measures), so I am never tempted to filter from a fact table.

 

(Not that it matters since you've solved the problem, I just wanted to add the additional context since I was once confused about lookup tables and why I wouldn't just use the values in the fact table since they were right there)

 

 

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
piyush0308 Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

Hi McNulty,

 

I am not sure about it, but as per my understanding the syntax of of Calculate 'Calculate(Expression,<filter expression1>,<filter expression2>....)'. Since you have applied a filter using date table and not using fact, this might be a case it is not working.

 

But as a best practice, we use the filters from dimension table not from fact table.

 

Can you please elaborate your requiremnt why do you want to use slicer from fact table?

 

konstantinos Senior Member
Senior Member

Re: Compare previous and new sales

@McNulty In order the formulas to work ( Time Intelligence ) you need to use fields from the Date table in your graph/table in rows or as a slicer. 

You can add a  calculated column in the one column date table    

Year = YEAR( DateTable[Date])   and Month = Month(DateTable[Date];"MMM")  

and then use this fileds for graphs

 

 

 

 

Konstantinos Ioannou
enocperez Frequent Visitor
Frequent Visitor

Re: Compare previous and new sales

Hi and I am new to DAX  is there a video out there on how this is done?

CheenuSing Super Contributor
Super Contributor

Re: Compare previous and new sales

Hi Konstantinos,

 

I am facing similar challenges.  I have a Calendar table which is populated on the range of dates betwen minimum of Sales Date and maximum of sales date.  The Calendar table thus built has all the contiguous dates. Say between 01/07/2013 and 15/05/2016.

 

I want to compute the Sales for the period '01/04/2016' and '15/04/2016' and compare it with the sales for the period '01/05/206' and '15/05/2016'. A variant of month-on-month sales.  When I use previous month in the expression it gives me total of entire Apr 2016 and not just the 1-15 of Apr.  

How to achieve this ?

 

Also when I use a measure CurYr = MAXX(Calendar,Calendar[FullDate]), I get the result as 2016 which is correct. But I am unable to use this measure in any other comparison inside a calculate expression and give me error like A function Caculate has been used in a true false expression that is used as a table filter expresion. This is not allowed.

 

Thank you in advance for your help.

 

CheenuSing

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,516)