Last Month Data

[Anonymous]

Hello I have a very simple issue,

Below is the data set,

I always need previous month count, like in current month. I need September data. PLease help.

[edhans]

You need to create a date table for this to work, then it is a very simple solution.

1. Create a date table. I just explained the easiest way to do this in another post today.
2. Relate the Date field in the date table to the date field in your data set.
3. Drag the date from the Date table (not your data set) to the table or matrix visual you are using.
4. Create this measure (it assumes you have another measure Total Sales that is something like = SUM(SalesData[Sales])):
   
   

   =CALCULATE(
   	[Total Sales],
   	PREVIOUSMONTH('Calendar'[Date])
   )

That will calculate your sales for the previous month. edit: fixed an extra comma in my formula above. Now it works. 🙂

[Anonymous]

Thanks yes, I have a date table which connects with other data table

Here is my measure = TOTAL_SALES = SUMX(Filter('SALES_TABLE','SALES_TABLE'[COLOR]="Blue"),'SALES_TABLE'([Amt])

Measure 2 = CALCULATE ([TOTAL_SALES],  PREVIOUSMONTH('DATE_Table'[Date_Key]))

But as you can see, it returens all the previous months, if I remove Year_Month column, it dows not show any data

[Ashish_Mathur]

Hi,

What is the problem with this output?  What result are you expecting?

[Anonymous]

I just want to get one value SUM of Last Month, but it is returning sum of each month like 15 rows or so.

[Anonymous]

Not entirely clear on what you are looking for, but have you given OPENINGBALANCEMONTH or PARALLELPERIOD try?  

[Anonymous]

@AnonymousI'm looking for very simple output only sum of last month's sales.

[Anonymous]

@Anonymousand @Ashish_Mathur

Just trying to simplyfying my question

[Anonymous]

Let's see if this gets in the right direction:

[Anonymous]

@AnonymousThanks Nick, but how do I get on previous month sum not the running total. Like In OCT I only wnat to see Sep totals.

[Anonymous]

You don't need to have the "Total Sales" measure in the table.  Removing that measure, and only having the Previous Month Sales, will show October on the row, but will have September's sales.  Though that could lead to some confusion.

Another idea could be to use ParallelPeriod, which shows the grand total of the previous month, regardless if you are at a lower granularity  

[Anonymous]

@Anonymouscan I send you my PBIX, it is very simple file with 2 tables

[edhans]

Sending the PBIX would have saved a lot of time from the start.

---

@Anonymous wrote:

@Anonymouscan I send you my PBIX, it is very simple file with 2 tables

---

[edhans]

I don't understand what you mean by "it returns all of the previous months." It should. It isn't cumulative. March will show Feb, Feb will show Jan, Jan will show Dec of previous year.

Show me exactly what the issue is, as you only showed "Measure 2" and not "Total Sales"

The first thing to check though is make sure any date data in your table is coming from the Date table, not the date field in your sales table. The date intelligence functions require the Calendar/Date table to be used in the visuals to work right.

[Anonymous]

So here is my fact table and date table, date keys in fact are coming from Date table.

[Anonymous]

Here is Total Sales

TOTAL_SALES = SUMX(Filter('SALES_TABLE','SALES_TABLE'[COLOR]="Blue"),'SALES_TABLE'([Amt])