Best way to see filtered rows of a table when the filter is compared with a slicer value

[oceany]

Hi! 

I have a tableA that has 'start date' and 'finish date' columns.

Let us say tableA looks like this

Row Number	start date	finish date
1	2022-01-01	2022-02-15
2	2022-02-01	2022-08-01
3	2022-02-14	2022-03-01
4	2022-03-01	2022-03-15
5	2022-02-27	2022-05-01

I would like to allow a user to select a date among these dates [2022-01-31, 2022-02-28, 2022-03-31], so I created a tableB with a column EndofMonth, and used it as a slicer. (I thought I could use SELECTEDVALUE('tableB'[EndofMonth]).

Now if a user selects 2022-02-28 in the slicer, I would like to show this table below. Basically, it is filtered where start date>= selected value && finish date<= selected value. I tried to created a calculated column which looks like col = CALCULATE(TABLE, FILTER( ,[start date>=selected value && [finish date<=selected value)), but it returns blanks values. 

Row Number	start date	finish date
2	2022-02-01	2022-08-01
3	2022-02-14	2022-03-01
5	2022-02-27	2022-05-01

Is there a way to do this using a slicer? Thank you!

[v-yueyunzh-msft · Accepted solution]

Hi, @oceany 

According to your description, you want to create a month-end table as a slicer, and then use the slicer to filter the original table.. Right?

Here are the steps you can follow：

(1)This is my test data: ‘Test’

(2) We can click "New Table" and enter DAX to automatically create our month-end table：

End of month =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( FIRSTDATE ( 'Test'[start date] ), LASTDATE ( 'Test'[finish date] ) ),
            "end", EOMONTH ( [Date], 0 )
        ),
        "end of month", [end]
    )
)

(3)Then we can create a measure : isDisplay

isDisplay =
VAR _slice =
    SELECTEDVALUE ( 'End of month'[end of month] )
RETURN
    IF (
        ISFILTERED ( 'End of month' ),
        IF (
            MAX ( 'Test'[start date] ) <= _slice
                && MAX ( 'Test'[finish date] ) >= _slice,
            1,
            0
        ),
        1
    )

(4)We can put the [end of month] column in the slice, and the fields we need in the table.After it , we need to put the [isDisplay] measure in the “Filter on this visual” and configure it like this :

Then we can meet your need now.

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Aniya Zhang

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

[oceany]

You are a genius👍!!! Thank you!

[amitchandak]

@oceany , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

or do you need a table

https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[oceany]

Thanks for your answer! I saw the DAX function below from the example you shared. How would I modify that one to filter my table? I need to show the same columns of the tableA, not countx. Also, can I use SELECTEDVALUE('tableB'[EndofMonth]) in the formula?

Day by Month = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),'Data'[id],'Date'[Date]),'Date'[Date]))