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',[Value by day of Month2]),(DATESBETWEEN(calen[Date],[Primer_Periodo],[Ultimo_Periodo])))
Primer_Periodo EDATE(LASTDATE(calen[Date]),-[Months])
Ultimo_Periodo EDATE(LASTDATE(calen[Date]),+[Months])
Months - 3
if I put the first and last period measurement on a table I get the correct values, but I don't know how to paint it so that on a chart I do well.
  • 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. 

11 Replies

  • 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))
  • camargos88's avatar
    camargos88
    Community Champion
    5 years ago

    nuriac ,

     

    I understood you wanted the sum of the period.

    Check the new file.

    You need a disconnected table for it.

    Be aware that I don't have values for all months, so a gap may show up.

    • nuriac's avatar
      nuriac
      Helper III
      5 years ago

      camargos88  GRACIAS! pero no acaba de funcionar

       

      para filtrar por fecha me funciona, pero si voy a filtrar por mes (septiembre) no funciona, da error. Porque?

  • camargos88's avatar
    camargos88
    Community Champion
    5 years ago

    nuriac ,

     

    Are you filtering on by month, or month/year ? It changes the code in the measure.

    • nuriac's avatar
      nuriac
      Helper III
      5 years ago

      @camargos88

      I have two filters, one month and one year. Why doesn't it work like that? that I have to change?

    • nuriac's avatar
      nuriac
      Helper III
      5 years ago

      @camargos88

      Hello

      Could you help me? that I have to change to the extent so that I can filter by month and year?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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