cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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?
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: 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.2-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Frequent Visitor

Re: FILTERING - date cycles - start and end given

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. 

POWERbi.png

 

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
    )
 

POWERbi2.png

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

 

Thank you one more time for your help!

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: 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.2-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Frequent Visitor

Re: FILTERING - date cycles - start and end given

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. 

POWERbi.png

 

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
    )
 

POWERbi2.png

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

 

Thank you one more time for your help!

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors