Forum Discussion
Last Month Data
You need to create a date table for this to work, then it is a very simple solution.
- Create a date table. I just explained the easiest way to do this in another post today.
- Relate the Date field in the date table to the date field in your data set.
- Drag the date from the Date table (not your data set) to the table or matrix visual you are using.
- 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. :-)
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
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.So here is my fact table and date table, date keys in fact are coming from Date table.
Hi,
What is the problem with this output? What result are you expecting?
I just want to get one value SUM of Last Month, but it is returning sum of each month like 15 rows or so.
