Line Chart Year-Over-Year Filtered by Month and Day / How Do I Apply a DAX Filter to a Chart?

[TimNYC]

I'm trying to build a line chart that shows year-over-year sales growth and updates dynamically based on the yesterday's date. I want it to look just like in the attched picture, except the X-Axis should stop at Feb 21 and not show dates Feb 22 - 29. How can I accomplish this?

X-Axis: Contains Month and Day from a date hierarchy

Legend: Contains Year from the same date hierarchy

Date Table: Contains January 1 - Feburary 21 of years 2017-2020. Feb 22 - Dec 31 are not in the table for any year.

When I first built the line chart it included every month of the year, even though my Date table only includes the months of of Jan / Feb. I was able to filter out month Mar - Dec by using a Top N filter based on sales in each month. Setting N = 12 effectievely filters out all months that don't have any sales (becaues they aren't included int he date table). However, my chart still shows that sales drop to zero for Fed 22 - Feb 29. I don't to show these dates at all, but I cannot apply filter on Day because that would also remove days Jan 22 - Jan 29.

What I need is to apply this filter to my line chart:

(MONTH(date) < MONTH(Today()-1)) OR (MONTH(date) = MONTH(Today()-1) AND DAY(date) <= DAY(Today()-1)

But I cannot figure out how to apply a DAX filter to my chart. Isthis possible? Is there any way to use the filtering pane to accomplish the same goal?

[amitchandak]

You are using a visual filter. There you can use the relative date filter(Drag your calendar date ). Use  Is in the last 5 years, including today. That should workout

[TimNYC]

Thank you for your reply. I tried to use the relative date filter, but I still cannot remove days 22 - 29 of February while still including days 22 - 29 of January. Would you please elaborate on how I can use the relative date filter to accomplish this?

Thank you

[amitchandak]

Are you using Datesytd or totalytd like?

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Please share the formula, you are using

[TimNYC]

Thank you for your time. I may have explained the issue poorly. I'm not trying to filter the data in the chart, which is probably how my question was interpreted, I'm trying to adjust the X-Axis so that it does not includes dates with no data. I'm going to post a new topic with a better explaination.

[amitchandak · Accepted solution]

Put the measure on the visual filter and use <> 0

[TimNYC]

Thank you! That did it. Such a simple solution... I don't know why it didn't occur to me before I posted.

[TimNYC]

The measure I am using in the line chart values is:

Quantity Year to Date = TOTALYTD('append'[Quantity],dates[Date])

The embedded 'append'[Quantity] measure is:

Quantity = CALCULATE(SUM('append'[sourceQuantity]))

To clarify, I'm not trying to adjust the values in the chart. I'm trying to remove the last few days of February from the X-Axis so that it doesn't look like sales plummit to zero on Feb 22nd.