Compare previous and new sales

[McNulty]

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?

[piyush0308 · Accepted solution]

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

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

[enocperez]

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

[McNulty]

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

[piyush0308]

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?

[leonardmurphy]

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)

[konstantinos]

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

[McNulty]

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?

[amysmith]

Hi,  if you wish, you will be able to learn more here:

https://www.udemy.com/power-bi-business-intelligence-with-query-onedrive-and-flow/?couponCode=PB7773...

[konstantinos]

@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

[CheenuSing]

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

[Twister8]

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?