cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

11 REPLIES 11
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))

Frequent Visitor

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

Frequent Visitor

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

Frequent Visitor

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?

Skilled Sharer

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

Konstantinos Ioannou
Frequent Visitor

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?

Helper I
Memorable Member

@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
Community Champion

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.

CheenuSing

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

Proud to be a Datanaut!
Helper II

Hello everybody,

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors