Chart with datesbetween

[nuriac]

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]

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

[nuriac]

Hola, 

no acabo de encontrar la solución a esto. Adjunto pbix a ver si puedes ayudarme. 

Creo que el problema está con los calendarios, pero no logro averiguarlo. La medida que utilizo en la tabla está hecha con otro calendario y no filtra bien. 

https://drive.google.com/file/d/1kzfKsanPVnBhsxuRgHZ0bnAj0CwEEG0Q/view?usp=sharing

[Anonymous · Accepted solution]

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]

@nuriac ,

Can you share you pbix ?

[camargos88]

@nuriac ,

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

[nuriac]

@camargos88

Hello

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

[nuriac]

@camargos88

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

[camargos88]

@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]

@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]

@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]

@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))