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 dateend dateUnit$ value
1/1/20101/1/2011A100
1/3/20101/3/2011B200
1/4/20101/4/2011C300
1/5/20101/5/2011D100
so onso on  
until 2020until 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/20101/1/2011A100

 

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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]))

       

       

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

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