Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
ID | CREATED | CYCLE START | CYCLE END | DESCRIPTION |
1 | 03-17-19 | 08-01-19 | 08-31-19 | b |
2 | 02-17-19 | 07-01-19 | 07-31-19 | a |
3 | 05-01-19 | 08-01-19 | 09-30-19 | bc |
4 | 05-02-19 | 07-01-19 | 08-31-19 | ab |
5 | 05-07-19 | 09-01-19 | 09-30-19 | c |
6 | 04-15-19 | 07-01-19 | 09-30-19 | abc |
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:
Solved! Go to Solution.
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!
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |