I see...that could be bothersome having unwanted months showing up.
I would suggest that you have at least complete months in your Date table covering your Data date range, so you could redefine Date as:
Date = CALENDAR ( EOMONTH ( MIN ( Data[Date] ), -1 ) + 1, EOMONTH ( MAX (Data[Date] ), 0 ) )
Given the way SAMEPERIODLASTYEAR, DATEADD and other time intelligence functions work, you will get funny results without complete months.
I updated my PBIX file above, and when 2017 is selected, a Month slicer will show only month 1.
On your question of why your intended behaviour isn't the default behaviour: Firstly, I agree that your intended behaviour is probably common to many businesses and I have seen it before. However, the time intelligence functions are intended to work with a Date table, without considering the exact dates that may be present in your fact table(s). It is possible that January has passed, but you had transactions on only 1-3 January. There is no way the DAX engine could know that January had concluded from the fact table alone, unless 'zero' rows were created for dates without transactions, or some other flag showed the last date.
Perhaps another approach would be to include full months in the Date table, but also include a binary column that indicates whether a date has passed or not, and that column could be referenced in your measures (rather than my approach which assumed the last date in the entire fact table is the latest date).
could you please help me with construction of formula to calculate Sales MTD LM (Last month)?
So I have standard measure for Sales MTD
Sales MTD = TOTALMTD(SUM(Data[Amount]);'Date'[Date])
And I would like to have measure to show me Sales MTD for last month - so when now is 17.1.2017, I have sales from 1.1.2017 to 17.1.2017. And then I would like to have Sales MTD LM from 1.12.2016 to 17.12.2016.
Is it possible, when the year in filter is set to 2017?
I would suggest a similar pattern to the YTD LY measure.
You can use DATEADD instead of SAMEPERIODLASTYEAR in this case.
INTERSECT(...) returns the selected date range limited by the latest date in the Data table, then DATEADD shifts it one month earlier.
Sales MTD Last Month (limited by last date in Data table) = VAR DataMaxDate = CALCULATE ( MAX ( Data[Date] ); ALL ( Data ) ) RETURN CALCULATE ( [Sales MTD]; DATEADD ( INTERSECT ( VALUES ( 'Date'[Date] ); DATESBETWEEN ( 'Date'[Date]; BLANK (); DataMaxDate ) ); -1; MONTH ) )
First of, thanks for all the help on the issue @OwenAuger however, I have a problem which I couldn't resolve at all since I am kind of beginner with DAX.
My dataset has unique Sales Year-Month-Day values from 01.01.2016 to 05.20.2017 in date format and was related with a Calendar Date column which ends at 05.20.2017 as well since it's the last data collection date.
First I was struggling to compare MoM for 2016 and 2017 sales. For example, May of 2017 contains only 20 days of data and I wanted to compare the same date range for May of 2016; however, it was fetching all the 2016 data till the end of the year. I solved that issue with your explanation.
For this year sales I used:
This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESYTD('Calendar'[Date].[Date]))
And for last year sales:
Last Year Sales = VAR DataMaxDate = CALCULATE ( MAX ( 'Calendar'[Date] ); ALL ( 'Calendar'[Date] ) ) RETURN CALCULATE ( [This Year Sales]; SAMEPERIODLASTYEAR ( INTERSECT ( VALUES ( 'Calendar'[Date].[Date] ); DATESBETWEEN ( 'Calendar'[Date].[Date]; BLANK (); DataMaxDate ) ) ) )
Since my intention was comparing apples to apples, this was the result I was looking for. I finally was able compare the first 20 days of May 2016 and first 20 days of May 2017.
However, now I am not able to visually filter any of the months. I mean, when I click Last Year Sales, it's automatically accumulating This Year Sales as well. Please see below:
My intention is:
1. To be able to individually filter every year's sales like I was able to before.
2. If I cannot filter individually because of the formulas I used, I am kind of looking for a new way to compare last year's and this year's sales. Like I mentioned, last data date must be taken into account for last year's datas.
3. I created a different Calendar.[Date] and columns related with my sales date data. (Format is in date like 01.01.2016)
Thanks for all the help, rgrds,
When you have a clustered column chart with two or more measures as Value fields, visual filters resulting from clicking on the bars are applied to the axis field.
In your example, the visual filter is applied to the month dimension on the axis to include only "May", but both measures are still visible.
You can de-emphasise bars for a particular measure by clicking on the legend, but this can't be done simultaneously with an axis dimension filter. Also, I don't think this 'filters' any other visual in any way.
Also I couldn't work out what the $3.68m represents. Are you saying that's the total of Last Year Sales and This Year Sales?
@OwenAuger: I tried your DAX formula and it works, to an extent. It works for the current year. I'm looking to display a table that shows the following three columns: [Year], [YTD], [YTD Last Year].
To test your formula I built a simple Excel sheet with two columns: [Sale Date] and [Sales] and populted it with every date between 1/1/2015 and today (8/18/2017) and $1.00 of Sales on each day (hey, makes it easy to test, right?). This table is joined to a fully populated Date table with dates ranging back and forward several years.
After adjusting your formula and building my table, I see this:
2017 is showing correct LYTD value of 231 (one over this year because of Feb. 29 2016) but Year 2016 should show 230. Instead it show 365.
Seems LYTD should be a VERY simple calculation in DAX. (MDX had the function ParallelPeriod that made this SUPER EASY!) I've spent hours pouring over blogs, forums and technet and still can't seem to get it.
Thanks in advance.