Forum Discussion
YTD last year DAX
- 9 years ago
It should work fine in Power BI.
You didn't have any dates in your Data table beyond 3 Jan 2017 did you?
Here is a sample PBIX file with your data posted above where the measure is working:
I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.
Have a play with that - there must be some difference in your model if it is not working.
Owen
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,
- OwenAuger9 years ago
Super User
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?