Forum Discussion
Filtering data for Custom week and Custom month date range
I have source data columns like Target Date, Sales. Target date could be any date past /future dates. I have a requirement to display two different charts one for weekly view and another one for Monthly view.
1. When a user opens the report it should automatically show the weekly chart with current week data ( Which will be starting from Mon - Sun data)
2. Monthly chart should show the current month data for example Nov'22. Month date should start from 1st Monday in Nov i.e, 7th Nov 2022 to until 4th Dec.
Please help me with solution for this requirement and there shouldn't be any manual filters it should filtered based on current system date.
Much appreciated.
Hi JaganMohan ,
Create a calendar table:
calendar = CALENDAR(EDATE(MIN('Table'[Target Date]),-1),EDATE(MAX('Table'[Target Date]),1))Then create measures like below and add then to corresponding visual filter and set value = 1.
weekly = IF(WEEKNUM(SELECTEDVALUE('Table'[Target Date])) = WEEKNUM(TODAY()),1,0) monthly = var start1 = CALCULATE(MIN('calendar'[Date]),FILTER('calendar',YEAR('calendar'[Date]) = YEAR(TODAY())&&month('calendar'[Date])=MONTH(TODAY())&&WEEKDAY('calendar'[Date],2)=1)) var end1 = CALCULATE(MIN('calendar'[Date]),FILTER('calendar',YEAR('calendar'[Date]) = YEAR(EDATE(TODAY(),1))&&month('calendar'[Date])=MONTH(EDATE(TODAY(),1))&&WEEKDAY('calendar'[Date],2)=1)) return IF(SELECTEDVALUE('Table'[Target Date])>=start1&&SELECTEDVALUE('Table'[Target Date])<end1,1,0)
5 Replies
Sure. But first please supply data to play with. You can place the file on a shared drive and post a link to it in here. Don't forget to grant public access rights to the file.
Here is the sample data file:
daXtreme - I'm able to get the weekly logics view. Can you please help me with Monthly view. Many thanks in advance.
Just came back from hospital. Will have a look at this as soon as I can.
Hi JaganMohan ,
Create a calendar table:
calendar = CALENDAR(EDATE(MIN('Table'[Target Date]),-1),EDATE(MAX('Table'[Target Date]),1))Then create measures like below and add then to corresponding visual filter and set value = 1.
weekly = IF(WEEKNUM(SELECTEDVALUE('Table'[Target Date])) = WEEKNUM(TODAY()),1,0) monthly = var start1 = CALCULATE(MIN('calendar'[Date]),FILTER('calendar',YEAR('calendar'[Date]) = YEAR(TODAY())&&month('calendar'[Date])=MONTH(TODAY())&&WEEKDAY('calendar'[Date],2)=1)) var end1 = CALCULATE(MIN('calendar'[Date]),FILTER('calendar',YEAR('calendar'[Date]) = YEAR(EDATE(TODAY(),1))&&month('calendar'[Date])=MONTH(EDATE(TODAY(),1))&&WEEKDAY('calendar'[Date],2)=1)) return IF(SELECTEDVALUE('Table'[Target Date])>=start1&&SELECTEDVALUE('Table'[Target Date])<end1,1,0)
