Line graph to stop showing future Quarters

[devika]

Hi All,

I have connected to Oracle SQL. I used Year and Quarter from the Date hierarchy for the X-axis. I haven't created a separate Date table. There is a data lag in the dataset. As you can see, 'Maxofdate' is five weeks behind from today, and this is going to be subjective. I want the line graph to stop showing at 'Maxofdate-Quarter' and not showing the future Quarters.

The below graph should show only until 2023 Q1, not other Quarters with blanks.

Creating a date table won't help, and I can't use visual or page filters to remove blanks; I can use filters only in the Measures.

check =

VAR MinDate = Min([DATE])

VAR MaxDate = Max(DATE])

RETURN

CALCULATE(IF(MAX[DATE]) < MinDate || MAX([CASE_DATE]) > MaxDate,BLANK(),[answer]))

Any thoughts are much appreciated.

[amitchandak · Accepted solution]

@devika , Try like

check =

VAR MinDate = MinX(allselected(Date), [DATE])
VAR MaxDate = MaxX(allselected(Date),DATE])
RETURN
CALCULATE(IF(MAX([DATE]) < MinDate || MAX([CASE_DATE]) > MaxDate,BLANK(),[answer]) )

[devika · Accepted solution]

Thanks Amit, it works perfectly.

[amitchandak · Accepted solution]

@devika , Try like

check =

VAR MinDate = MinX(allselected(Date), [DATE])
VAR MaxDate = MaxX(allselected(Date),DATE])
RETURN
CALCULATE(IF(MAX([DATE]) < MinDate || MAX([CASE_DATE]) > MaxDate,BLANK(),[answer]) )

[igonzalezb]

Hi. This has a side-effect, I believe. If you put this measure in a table visual, the grand total of the year will be blank when we are in the current year. Any way to avoid this? the grand total should be the last nonblank value of the measure.