Using only the top of a between date slicer.

[gwyn]

Hi,

I'm looking to create a LASTNONBLANK measure for a column in a table visual. It works fine, but if the user moves the 'date between' slicer min(date) to beyond when the lastnonblank was logged it goes blank. 

I need the max(date) in the date slice to still effect to LASTNONBLANK measure. Is there some way to have the date table that the LASTNONBLANK measure scans over ignore the min(date) slicer values for the page?

DIGI STATUS =

VAR _START_TIME = ALL ( 'T_DATE'[DATE] )  -- This to ignore all slicers on the date

VAR _END_TIME = MAX ( 'T_DATE'[DATE] ) -- This to still allow the max(date) slicer

RETURN

LASTNONBLANKVALUE(
DATESBETWEEN('T_DATE'[DATE], _START_TIME, _END_TIME),   -- This is the bit where I fall down
MAX('HUBSPOT_HISTORIC_STATUS'[DIGITAL_ADVERTISING_STATUS])
)

There is a relationship between the dates table 'T_DATE' and the 'HISTORIC_STATUS' table.

[AllisonKennedy]

@gwyn I'm not quite sure what you're trying to acheive, but the ALL() function returns an entire table and you need a single scalar value. Dax.Guide is a great resource for what each function does. 

Try using MINX for your start time: 

DIGI STATUS =

VAR _START_TIME = MINX(ALL ( 'T_DATE'[DATE] ) ,'T_DATE'[DATE] )  -- This to ignore all slicers on the date

VAR _END_TIME = MAX ( 'T_DATE'[DATE] ) -- This to still allow the max(date) slicer

RETURN

LASTNONBLANKVALUE(
DATESBETWEEN('T_DATE'[DATE], _START_TIME, _END_TIME),   -- This is the bit where I fall down
MAX('HUBSPOT_HISTORIC_STATUS'[DIGITAL_ADVERTISING_STATUS])
)

[gwyn]

Thank you, If i could send you a beer virtually I would.

[AllisonKennedy]

@gwyn  Best reply ever - much thanks. Did you solve your problem then or still have more questions?

[gwyn]

All Sorted, I was clearly just trying to jam the whole (ALL(T_DATE) table into the DATESBETWEEN function where it wanted the MINX date value. I have successfully worked around the lower part of the slicer.  🍻