Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Filter Data based on Certain Date Range

Hi all , need some help to filter the data based on certain range Below are the sample data that I have. Would like to filter the data based on start date and end date to get target output based o...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to set the two relationships between Calendar table and QCDailyTarget table to inactive.

    Or your visual will be impacted by the relationship.

    I suggest you to create a measure to filter your visual.

    FilterMeasure = 
VAR _RANGESTART =
    MIN ( 'Calendar'[Date] )
VAR _RANGEEND =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        SELECTEDVALUE ( QCDailyTarget[Start Date] ) <= _RANGESTART
            && SELECTEDVALUE ( QCDailyTarget[End Date] ) >= _RANGEEND,
        1,
        0
    )

    Add this measure into visual level filter and set it to show items when value = 1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Mahesh0016's avatar
Mahesh0016
Icon for Super User rankSuper User
3 years ago

Anonymous 

 

 

Calculated Column Filter =
CALCULATE (
    VALUES ( Filter_Data_Table[Target Output] ),
    FILTER (
        'Calendar_Table',
        'Calendar_Table'[Date] <= EARLIER (Filter_Data_Table[Start Date] ) && 'Calendar_Table'[Date] >= CALCULATE(MIN(Filter_Data_Table[End Date]),USERELATIONSHIP(Filter_Data_Table[End Date],Calendar_Table[Date])) )
    )
**If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Mahesh0016 , this produces same output as what i have tried and doesnt filter it out based on the dates . May i ask if you put the relationship only on end date to the calendar? Also , sorry for the bad explaination at the question but what i want is all the data shown based on date filter .