DAX Filter Data based on Certain Date Range

[Anonymous]

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 on calendar (which i use slicer in the dashboard).  I also have a calendar table with date column to filter all of the data in the report

This is what i tried but it still did not give what I wanted : 

Calculated Column Filter = CALCULATE (

    VALUES ( QCDailyTarget[Target Output] ),

    FILTER (

        'Calendar',

        'Calendar'[Date] <= EARLIER (QCDailyTarget[Start Date] ) && 'Calendar'[Date] >= EARLIER (QCDailyTarget[End Date] )

    )

)
 

My expected output is will only show all data and 1 data for Jr QC which is in the range of start date and end date. Please help give me a better approach to this problem

Thanks and Regards,

Hairul

[Mahesh0016]

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

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 .

[Mahesh0016]

Hello @Anonymous please can you share Data modeling of calendar and QCDailyTarget tables.

[Anonymous]

Hi , Unable to share since have a lot of tables linked  (Unable to see clearly) .Both of the start date and end date linked on date column

[tamerj1]

@Anonymous 
If you want to keep the relationship perhaps for other visuals and measures, you can modify my previour filter measure as follows.

FilterMeasure =
IF (
    NOT ISEMPTY (
        FILTER (
            ALL ( QCDailyTarget ),
            QCDailyTarget[Start Date] >= MIN ( 'Calendar'[Date] )
                && QCDailyTarget[End Date] <= MAX ( 'Calendar'[Date] )
        )
    ),
    1
)

[Anonymous]

Hi, your solution only show 1 row data .. Sorry for bad explaination at the question but i want actually are all type and calculated filter are shown except for Jr QC which will show 1 data which are filtered at respective date 

[tamerj1]

@Anonymous 

Please explain further 

[Anonymous]

Hi @tamerj1 , 

So referring to my initial question , the expected output are Jr QC with the value of 3 and with other types  after using the month and year slicer December 2022.   The result i am getting right now is getting all 3 rows of Jr QC and other types ( which intended only 1 Jr QC).

[v-rzhou-msft · Accepted solution]

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.

[tamerj1]

Hi @Anonymous 

Please place the following measure in the filter pane of the visual or the report page, select "is not blank" then apply the filter.

FilterMeasure =
IF (
    NOT ISEMPTY (
        FILTER (
            QCDailyTarget,
            QCDailyTarget[Start Date] >= MIN ( 'Calendar'[Date] )
                && QCDailyTarget[End Date] <= MAX ( 'Calendar'[Date] )
        )
    ),
    1
)