Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX formula help

I would need help creating DAX and designing data model. Table1:   start date end date Unit $ value 1/1/2010 1/1/2011 A 100 1/3/2010 1/3/2011 B 200 1/4/2010 1/4/2011 C 300 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Check the formula below.

Measure = 
IF (
    ISFILTERED ( 'CALENDAR'[Date] ),
    IF (
        SELECTEDVALUE ( 'CALENDAR'[Date] ) >= SELECTEDVALUE ( 'Table'[start date] )
            && SELECTEDVALUE ( 'CALENDAR'[Date] ) <= SELECTEDVALUE ( 'Table'[end date] ),
        "y",
        "n"
    ),
    IF (
        TODAY () >= SELECTEDVALUE ( 'Table'[start date] )
            && TODAY () <= SELECTEDVALUE ( 'Table'[end date] ),
        "y",
        "n"
    )
)

However it is not supported for date slicer to display as calendar visual.

You may need to use custom visual instead.

 

Best Regards,

Jay

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous  Thanks for helping me on this.

I written DAX which abosutely working fine also added Min(Date) to enable calendar option in date slicer.

 

var _max = MIN('Date'[Date])
var _today = SELECTEDVALUE('Date'[Date],TODAY())
Return
IF (
ISFILTERED ( 'Date'[Date] ),
CALCULATE(value,FILTER('Table1',_max>=[DATE2] && _max<=[DATE1])),
CALCULATE(value,FILTER('Table1',TODAY()>=[DATE2] && TODAY()<=[DATE1]))
)

 

when I ran the report by clicking clear the filters ,its showing the values default to todays date. But the slicer value is still showing as Min(Date) value, in my case it showing as 1/1/2010.

 

Is there any way to pass today's date to slicer as default value.