Forum Discussion

JaganMohan's avatar
JaganMohan
New Member
3 years ago
Solved

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 Mont...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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)

     

     

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
3 years ago

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.