Forum Discussion
Need to set filter for text
Hi All,
I have scenario in the report I have 3 bookmarks Daily,MTD and YTD. and i have slicer for date range. Bookmarks will work based on slicer date range selection.
I need to create a visual (Table or Matrix ) for below data but now when I select daily bookmark the visual should show data for last day of the date range. for MTD it should pick month data based on date range selection. Can any one help here .
| Transaction Date | Item Number | Org | Old Number | New Number |
| 01/01/23 | 12345 | ZXY | A | B |
| 01/06/23 | 12355 | ZXY | B | A |
| 01/11/23 | 12365 | ZXY | A | B |
| 01/16/23 | 12375 | ZXY | B | A |
| 01/21/23 | 12385 | ZXY | A | B |
| 01/26/23 | 12395 | ZXY | B | A |
| 01/31/23 | 12405 | ZXY | A | B |
| 02/05/23 | 12415 | ZXY | B | A |
| 02/10/23 | 12425 | ZXY | A | B |
| 02/15/23 | 12435 | ZXY | B | A |
| 02/20/23 | 12445 | ZXY | A | B |
| 02/25/23 | 12455 | ZXY | B | A |
| 03/02/23 | 12465 | ZXY | A | B |
| 03/07/23 | 12475 | ZXY | B | A |
Thanks in advance
Hi Anonymous ,
Here are the steps you can follow：
1. Enter data – Create a table.
2. Create mesaure.
Flag = var _min=MINX(ALLSELECTED('Date'),[Transaction Date]) var _max=MAXX(ALLSELECTED('Date'),[Transaction Date]) var _group=SELECTEDVALUE('Group_Slicer'[GroupGroup]) var _monthdate1= DATE(YEAR(_max),MONTH(_max),1) var _monthdate2= EOMONTH(_max,0) return SWITCH( TRUE(), _group="Daily"&&MAX('Table'[Transaction Date])=MAXX(FILTER(ALL('Table'),'Table'[Transaction Date]<=_max),[Transaction Date]),1, _group="MTD"&&MAX('Table'[Transaction Date])>=_monthdate1&&MAX('Table'[Transaction Date])<=_monthdate2,1, _group="YTD"&&YEAR(MAX('Table'[Transaction Date]))=YEAR(_max),1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Select Daily
Select MTD
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Anonymous ,
Here are the steps you can follow：
1. Enter data – Create a table.
2. Create mesaure.
Flag = var _min=MINX(ALLSELECTED('Date'),[Transaction Date]) var _max=MAXX(ALLSELECTED('Date'),[Transaction Date]) var _group=SELECTEDVALUE('Group_Slicer'[GroupGroup]) var _monthdate1= DATE(YEAR(_max),MONTH(_max),1) var _monthdate2= EOMONTH(_max,0) return SWITCH( TRUE(), _group="Daily"&&MAX('Table'[Transaction Date])=MAXX(FILTER(ALL('Table'),'Table'[Transaction Date]<=_max),[Transaction Date]),1, _group="MTD"&&MAX('Table'[Transaction Date])>=_monthdate1&&MAX('Table'[Transaction Date])<=_monthdate2,1, _group="YTD"&&YEAR(MAX('Table'[Transaction Date]))=YEAR(_max),1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Select Daily
Select MTD
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly