Forum Discussion

Resty's avatar
Resty
Frequent Visitor
7 years ago
Solved

FILTERING - date cycles - start and end given

Hello everybody:

 

I have a problem with how to filter my data to show what I would like it to show.

Here'a a sample data:

 

tblData

IDCREATEDCYCLE STARTCYCLE ENDDESCRIPTION
103-17-1908-01-1908-31-19b
202-17-1907-01-1907-31-19a
305-01-1908-01-1909-30-19bc
405-02-1907-01-1908-31-19ab
505-07-1909-01-1909-30-19c
604-15-1907-01-1909-30-19abc

 

I want to build the reports that show the data based on the date cycles - basicly months.

For example data for August 2019 should include all rows that have at least one day of August included from [CYCLE START] to [CYCLE END].

(if  date my filter range includes at lest one day of August - then I want for report to include all the records that have "b" in [DESCRIPTION] column of the sample table etc.)

 

For all the charts I use a Measure in tblData table.

I've build a CALENDAR table that has each day between min and max values of [CREATED], [CYCLE START] and [CYCLE END] dates, one column [Date].

Measure is:

 

Count of Issues =
VAR StartDate = MIN ( CALENDAR[Date] )
VAR EndDate = MAX ( CALENDAR[Date] )
RETURN
CALCULATE(COUNTROWS(tblData), tblData[CYCLE END ] > StartDate, tblData[CYCLE START] <= EndDate)
 
And it works wonderful.
At the reports I setup my charts with tblData data and values of "Count of Issues" Measure, and I put up a slicer with Date from CALENDAR table.
When slicing the Date - the Measure is calculating everything correctly.
 
But I face the problem when I want show the data [DESCRIPTION] in report as a table.
Tables: CALENDAR and tblData have no relationships set up.
 
So when I put a table in report to show the description - this table is not affected by the slicer - it shows all data rows.
 
I can't set the relatioship to CALENDAR table because:
-linking it to [CREATED] would not show the data that I want
-linking it to [CYCLE START] would exclude records that occur during multiple cycles and start before the start of filtered date (for example: filtering August would exlude record that starts in July and ends in August), the same with [CYCLE END].
 
I have tried to insert additional column with list of rows with Dates added between [CYCLE START] and [CYCLE END], that resulted with X duplicates of the row, each next with subsequentdate, where X is number of days between start and end of cycle... And That worked when I counted distinct [ID] values... but my dataset has a lot of additional numeric columns - and that multiplied each value of different reports by X... So I think that approach creats too much problems and work - there needs to be easier way that I'm not awere of.
 
I need to be able to filter the Date range and for the reports to show the data that occurs during that cycle.
(my charts are working - but I don't know how to force the table to be sliced correctly)
 
Does anybody know how to set up the slicer or filters to work with Date cycles in a situation like that?
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    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.

  • Resty's avatar
    Resty
    7 years ago

    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!

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.

    • Resty's avatar
      Resty
      Frequent Visitor
      7 years ago

      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!