FILTERING - date cycles - start and end given

Hi Resty ,

Firstly, you need to create three new columns in the Calendar table.

Year =
YEAR ( 'Calendar'[Date] )

Month =
MONTH ( 'Calendar'[Date] )

YM =
FORMAT ( 'Calendar'[Date], "yyyy-mm" )

Then you need use this new measure:

Count of Issues =
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Calendar'[Month] )
RETURN
    CALCULATE (
        COUNTROWS ( tblData ),
        YEAR ( tblData[CYCLE END] ) >= SelectedYear,
        YEAR ( tblData[CYCLE START] ) <= SelectedYear,
        MONTH ( tblData[CYCLE END] ) >= SelectedMonth,
        MONTH ( tblData[CYCLE START] ) <= SelectedMonth
    )

Now, you can use slicer with ‘Calendar’[YM] to filter your visual.

Best Regards,

Eads

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

Thank you v-eachen-msft !

Measure works and it opened my eyes for new posibilities!

Downside is that it works 'funny' for multiple cycles. You need to add YM column to table to show any data when multiple YMs are selected and it's duplicating the multi cycle records for each YM selected that they're in. 

I figured out second Measure that's not duplicating the records:

Count of Issues 2 = 
VAR DateMin =
    FIRSTDATE('Calendar'[Date])
VAR DateMax =
    LASTDATE('Calendar'[Date])
RETURN
    CALCULATE (
        COUNTROWS ( TEST_table ),
        tblData[CYCLE START] <= DateMax,
        tblData[CYCLE END] >= DateMin
    )

Basicly my biggest mistake was to not iclude the Measure in the table.

Thank you one more time for your help!