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. 

camargos88's avatar
camargos88
Community Champion
5 years ago

nuriac ,

You can use this measure:

_GrossSales = 
VAR _selectedDate = SELECTEDVALUE(financials[Date])
VAR _dateStart = EOMONTH(EOMONTH(_selectedDate, 0), -4) + 1
VAR _dateEnd = EOMONTH(EOMONTH(_selectedDate, 0), 4)

RETURN CALCULATE(SUM(financials[Gross Sales]),FILTER(ALL(financials[Date]), financials[Date] >= _dateStart && financials[Date] <= _dateEnd))

 

Check the attached file.

  • nuriac's avatar
    nuriac
    Helper III
    5 years ago

    camargos88 

    Hola, esto no me sirve porque lo que quiero es poder representar en un grafico los tres meses antes y después de la fecha que selecciono. y con esto no me aparece nada. He probado con tu .pbix y tampoco aparece

     

    _GrossSales =
    VAR _selectedDate = SELECTEDVALUE(CALENDARIO[Date])
    VAR _dateStart = EOMONTH(EOMONTH(_selectedDate,0),-4)+1
    VAR _dateEnd = EOMONTH(EOMONTH(_selectedDate,0),4)

    RETURN CALCULATE(SUMX('MEDIDAS PEDIDOS',[Value by day of Month2]),FILTER(ALL(CALENDARIO[Date]),CALENDARIO[Date]>=_dateStart&&CALENDARIO[Date]<=_dateEnd))