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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
McNulty
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
piyush0308
Frequent Visitor

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
piyush0308
Frequent Visitor

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

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

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

 

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).

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?

 

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 Madison Power BI User Group.
konstantinos
Memorable Member
Memorable Member

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

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?

 

 

@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

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!

Hello everybody,

 

I am new in DAX, please help me

 

I need show just last value month, for example this actual month is 201604, so:

 

month      values

2016-1     1

2016-2     5

2016-3     10

2016-3     10

 

So I need return the 20 in my result.

 

I am trying use but isnt work:

 

LastMonth= CALCULATE(SUM('MYtable'[Colum With Value]); PREVIOUSMONTH('MYTableTIME'[Date]))

 

However when I typed in my expression i can't see the result, because i dont have a total

 

When I use this:

LastMonth = CALCULATE([Colum With Value];DATEADD(MYTable[Date]; -1; MONTH))

the total is all months of all years, i need just last month, in my example month 03, its possible filter the actual year?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.