Best way to exclude calendar table months before data series starts

[adin]

Hi Guys,

I'm using a calendar table in my data model and have a measure that shows zero when there are blanks. The reason I'm doing this is that I want to show a complete time series even if the customer doesn't make purchases in a particular month. The problem I'm having is because I'm removing the blanks in my measure, now my entire calendar table is showing up in the bar chart (see below). I need the bar chart to start from when the data series starts and yet display empty months after that. 

What are some of the ways you guys have dealt with this (ideally in an automatic way)? I can probably come up with something but wanted to see what the best practices are out there (i.e. the most effective way of doing it.).

Thanks Guys!

[V-lianl-msft · Accepted solution]

Hi @adin ,

Create a measure like this and apply it to  visual level filter.

Measure = var min_ = CALCULATE(MIN(Sheet2[Date]),ALLEXCEPT(Sheet2,Sheet2[customer]))
var max_ = CALCULATE(MAX('calendar'[Date]))
return IF(max_>min_,1)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

One way is to add an IF into your expression to compare the MIN(Date[Date]) to the overall MIN of your fact table

CALCULATE(MIN(Table[Date]), ALL(Table)).  That way, any months will be blank prior to the overall min, and not show in the visual.

Regards,

Pat

[adin]

Would I use CALCULATE(MIN(Table[Date]), ALLSELECTED(Table)) if I want my bar chart to dynamically respond to which customer the user selected from a drop-down filter?