10-11-2016 03:26 PM - edited 10-11-2016 03:27 PM
I have a sales table with a column for the date and another one for sales amount.
I want to show only sales of current month and two months after and compare with sales same period last year.
The filter for the current month must change automatically without user interaction.
To do that I have created 5 columns in sales table:
- Beginning date current month
- Last day two months later
- Beginning date current month last year
- Last day two months later last year
- Conditional column that returns true if (date >= beginning date current month and <= last day two months later) OR (date >= beginning date current most last year and <= last day two months later last year).
Does anyone knows if there is an easier way to do that?
on 10-11-2016 03:42 PM
You can use TODAY() function to get the current date.
From ther you can calculate current month.
Then you use DATEADD() to calculate next two months.
Hope that helps.
on 10-12-2016 01:11 AM
Thank you, @soheil
I have done it the way I told you and it works nearly ok.
If I use a matrix I don't have any problem to compare years
- Rows for years.
- Column for month and day.
- Values are total amount
The problem is when I want to represent that in a chart. I would like to use line chart with two lines:
- One line for the sales current month and two months later (date by date).
- The Other line must shows sales last year current month and two months later (date by date).
Thanks & Greetings
on 10-12-2016 07:43 AM
Not sure if you can adapt this for your use case, but it sounds like it might help. Maybe not, but worth a look.
on 10-12-2016 01:10 PM
Actually, i hace to do another reportó showing sales amont last 4 weeks and week by week.
So, The article you refer to is perfect for this other case.
It seems I can do something similar with months. I hace to deep in little more.
Anyway, It's a very interesting post
As i have said before, many thanks!!
on 10-12-2016 01:16 PM
This may apply to your question or not.
I use this code to display the value of the last data point in a line chart in a Card visual above the chart since I don't want to show the values for all the data points.
VAR LstMthV = CALCULATE(LASTNONBLANK(Nursing_Data[Period Month],1))
VAR LstYrV = 2016
(MIN(Nursing_Data[Period Year]) && Nursing_Data[Period Month]=LstMthV))
on 10-13-2016 12:41 AM
It seems I have solved the issue. I have created three columns:
- Beginning date actual month = DATE(YEAR(TODAY());MONTH(TODAY());"01")
- Last date two months later = EOMONTH(TODAY();2)
- Data in three months period = IF(Sales[Date]>=Sales[Beginning date actual month] && Sales[Date]<=Sales[Beginning date two months later];TRUE();FALSE())
I have filtered report by last column to show only sales in three months time (one line of the chart).
To draw another line for same period last year. I have created this measure:
- Sales three months period last year = CALCULATE(SUM(Sales[Sales Amount]);SAMEPERIODLASTYEAR(Calendar[Date]))
Calendar is a date table.
Hope it helps,