VARIABLE: UNDESIRED BEHAVIOR

[PMPalex]

Hi, 

I have a line chart where I want it to show the data only until the selected month. This is the metric that I created with a variable that returns that month's number.

The result of the variable is 4. I check that this is the result, it's numeric format... all it's OK, but the graph shows all months

SeleccionMeses =

VAR MesSeleccionado=SELECTEDVALUE(PBI_AMOUNT[DATE_REF].[NroMes])

RETURN

CALCULATE ([IMPORTE];
ALL (PBI_AMOUNT[DATE_REF].[NroMes]);
PBI_AMOUNT[DATE_REF].[NroMes] <= MesSeleccionado
)

But if change the last line for the result of the variable it works!

PBI_AMOUNT[DATE_REF].[NroMes] <= 4

I have tried creating the variable outside and calling it in the function, that there were no format failures, etc ... but I can't get Power BI to use the result of variable (number 4) correctly.

Please, could someone help me with this issue?

Thanks in advance!!!!!!!

[Anonymous]

I'll give you a very good piece of advice: PLEASE REFRAIN FROM USING THE AUTOMATICALLY GENERATED CALENDAR HIERARCHIES. Instead, build your own calendars if you want to be on the safe side and develop without surprises.

Best
D.

[AlB]

Hi @PMPalex 

Can you share the pbix, or a simplified version of it that reproduces the problem?

[PMPalex]

Hi,

Thanks for your answer. Attach one simplified example of them.

https://wetransfer.com/downloads/7ebc1653cfa2e7bb7455c2483d7107e620190905084806/b932f6

If you change the last line of the variable for a especific value is the result that I need to get

Hoja1[DATE_REF].[NroMes] <= MesSeleccionado    ------>   Hoja1[DATE_REF].[NroMes] <= 5

THANKS!

[AlB]

The problem is that you are using Hoja1[DATE_REF].[NroMes] both in the slicer and in the axis of the chart. What the measure reads from SELECTEDVALUE is the value it gets from the axis. Plus there are some other issues with using the date hierarchy, which I would not recommend. Usually, selecting one month on the slicer would mean that only that month is displayed on the chart, since it's the same field on the axis.  But that doesn't seem to happen, probably because of what Power BI builds behind the scenes to implement the date hierarchy. 

I think the quickest solution would be to create another one-column table with he month numbers and use that as a slicer. The you read that with a SELECTEDVALUE and take the MAX(Slicer, value from axis) to limit the SUM. Make sure the measure returns BLANK if the value in the axis is above the value in the slicer, so that nothing is depicted on the chart.     

[PMPalex]

Hi,

Thanks for the answer!!  I'm not sure if I'm understand right the last part... I'm begginer yet 🙂

Can you return the file with metric or give me an example?

THANKS AGAIN!

[PMPalex]

I created the new column called "MES" and this metric but it doesn't work....  it is right? THANKS!

SeleccionMeses =

VAR MesSeleccionado=SELECTEDVALUE(Hoja1[MES])

RETURN

CALCULATE (SUM(Hoja1[IMPORTO_FST])*2;

ALL (Hoja1[DATE_REF].[NroMes]);

Hoja1[DATE_REF].[NroMes] <= MAX(MesSeleccionado;blank())

)

[AlB · Accepted solution]

@PMPalex

See the attached file for an example. Like I said, this is a quick and dirty solution. You should eschew the auto date/time feature altogether and create your own date table that can be used in the slicer The auto date/time feature can be turned off at

Options and settings->Options->  Current File -> Data Load ->Time Intelligence

and at 

Options and settings->Options->  Global -> Data Load ->Time Intelligence

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers