Forum Discussion

nuriac's avatar
nuriac
Helper III
5 years ago
Solved

Chart with datesbetween

Hello I want to make a chart showing me the information from 3 months before to 3 months after the month I'm filtering. I'm using the measure Pruebpedidos3 ? CALCULATE(SUMX('ORDER MEASURES'...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi nuriac 

    I think you need to get the result between select month +-3 by two Slicer (Year and Month)

    I build a sample to have a test.

    Build a calendar table.

    Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

    Build two slicers by year and month column in calendar table.

    Then build a measure:

    Measure = 
VAR _Y = SELECTEDVALUE('Calendar'[Year])
VAR _M = SELECTEDVALUE('Calendar'[Month])
VAR _SELDATE = DATE(_Y,_M, 1)
VAR _MAXDATE = EOMONTH(_SELDATE,+3)
VAR _MINDATE = EOMONTH(_SELDATE,-4)+1
RETURN
IF(MAX('Table'[Date])>=_MINDATE&&MAX('Table'[Date])<=_MAXDATE,1,0)

    Drag the measure into the Filter field in this table visual and set it show items when value = 1.Result is as below.

    As default it will show blank and  when you select Year = 2020, Month =3.

    You can download the pbix file from this link: Chart with datesbetween

     

    Best Regards,

    Rico Zhou

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi nuriac 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou