DAX formula help

[Anonymous]

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
1/5/2010	1/5/2011	D	100
so on	so on
until 2020	until 2022

if (Select date >=start date and select date<= end date ,"y","n") ,and then filter out the data set to "Y" .

User selects any date from the calendar in the slicer ,so  based on that the table 1 data should be displayed.

Example if user select 1/2/2010 then the data should display like below based on if else condition.

1/1/2010

1/1/2011

A

100

[Anonymous]

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]

@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.

[Fowmy]

@Anonymous 

Create the following measure, replace the correct table names and columns. Add a slicer for dates and make it single select.

Measure1 = 
VAR __DateSelected = MAX('Calendar'[Date]) Return
IF(
    __DateSelected >= max(Table[StartDate]) && __DateSelected <= max(Table[EndDate]) ,
    1,
    0
)

Insert a Table Visual on the canvas, add Start Date, End Date, and other fields you need, Go to Filter Pane and add the new measure and set to equal 1. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

There is no relationship between date and table 1. Does this formula works?

[Anonymous]

@amitchandak  thanks amit , the formula is working. But there are a couple of challenges.

1) The defult set to Today's date

2) User needs calendar option in slicer.

I have gone through below Video in You tube. this workaround is working but how to merge these two formuals.

https://www.youtube.com/watch?v=zhWtU0DynCk

My formula as below to set as default value to todays date.

WRITTEN =

var _max = SELECTEDVALUE('Date'[Date],TODAY())

return

calculate([WRITTEN PREMIMUM], filter('Active Policy', _max>=[DATE2] && _max<=[DATE1]))

[Anonymous]

@amitchandak  - Can you please help me on this.

[amitchandak]

@Anonymous , if there is no relation between date and table. It will work better. All or allselected might not be needed in that case 

[amitchandak]

@Anonymous , Try a measure like.

measure
var _max = maxx(allselected(Date), Date[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Start Date] <=_max && Table[End Date] >=_max))

or use all of allselected

measure
var _max = maxx(allselected(Date), Date[Date])
return
calculate(sum(Table[Value]), filter(all(Table), Table[Start Date] <=_max && Table[End Date] >=_max))