Auto select previous months

[Anonymous]

Hi guys,

I want to know if it is possible select one month, and the others selected automatically, like a MTD (I suppose). I have an alternative, using quarters checkbox, but, what i really want is select one month from the list.

Example: I choose the August month, and automatically select all previous months (Jan, fev, mar, apr, mai, jun, jul from 2015 and 2016).

What happens when i choose August:

What i want:

I know the time slicer app, but i really don't like this app.

[Vvelarde · Accepted solution]

@Anonymous

Try this:

Create a table with Months (1....12) Use this as Slicer. Don't related with your data table.

Create a Measure: 

ValuesChart =
VAR MAXMONTH =
    VALUES ( Months[Month] )
RETURN
    IF (
        VALUES ( Table1[Month] ) <= MAXMONTH,
        CALCULATE ( SUM ( Table1[Values] ) ),
        BLANK ()
    )

Use this measure in your chart.

[Anonymous]

Hi @Vvelarde,

I tried your solution but I'm with some doubts.

I created a table with a Month column (1,2,3,4...,12)

Then I create a measure, but I've doubts what is the table/column that I need to put in "CALCULATE ( SUM...)"

ValuesChart =
VAR MAXMONTH =
    VALUES ( Dimensão Data[Month] )
RETURN
    IF (
        VALUES ( Tabela1[Month] ) <= MAXMONTH,
        CALCULATE ( SUM ( Tabela1[Month] ) ),
        BLANK ()
    )

I've already a Table with a date, year, month.. but I didn't related them.

I have my Month slicer from my Tabela1 data

What is the chart configuration?

Thanks in advance,

Flávio.

[Vvelarde]

@Anonymous

hi, you have 

ValuesChart =
VAR MAXMONTH =
    VALUES ( Tabela1[Month] )
RETURN
    IF (
        VALUES ( DimensaoData[Month] ) <= MAXMONTH,
        ***YOUR COLUMN TO SHOW AS VALUE IN THE CHART***),
        BLANK ()
    )

In the chart Use DimensaoData for Axis and Legend.

Note: In you your DimensaoData the months are numeric. (1...12). If you don't have please add this column

[Anonymous]

@Vvelarde

I can't get the chart. What is wrong?

[Vvelarde]

what is the message in ver Detalhes

[Anonymous]

MdxScript(Model) (6, 46) miscalculation as 'OINV AND ORION' [Values Chart] was provided a table of various values where it was expected one value

[Vvelarde]

@Anonymous

I made a sample of this.

Sample

[Anonymous]

@Vvelarde thank you,

It is possible replicate your code in a card label, to show me a % ?

I'm using your code unsuccessful in my card label:

Var = 
VAR MAXMONTH =
    VALUES ( 'Calendar'[MonthNumber] )
RETURN
    IF (
        VALUES ( 'Calendar'[MonthNumber]) <= MAXMONTH;
        CALCULATE ( DIVIDE('OINV E ORIN'[Variação Current vs Previous Year tons Activos];'OINV E ORIN'[Consumo de clientes Tons Previous Year];0) );
        BLANK ()
    )

[Vvelarde]

@Anonymous

The measure fail becasuse There is not a unique Calendar's Month selected, in the chart this work because your Axis is the each month so Values is a unique row.

What is the %  that you want to show

[Anonymous]

my goal is to show in my card label the previous year variance in %. You're right, with this calendar I'm unable to show any result. There is other way using your code or the same logic?

my main code that I want the % is:

Variação Current vs Previous Year em % perda de Volume = DIVIDE('OINV E ORIN'[Variação Current vs Previous Year tons Activos];'OINV E ORIN'[Consumo de clientes Tons Previous Year])

"subcodes"...:

Variação Current vs Previous Year tons Activos:

Variação Current vs Previous Year tons Activos = 'OINV E ORIN'[Consumo de clientes Activos Current Year em Tons]-'OINV E ORIN'[Consumo de clientes Activos Previous Year em Tons]

Consumo de clientes Tons Previous Year:

Consumo de clientes Tons Previous Year = CALCULATE(SUM('OINV E ORIN'[Quantidade Tons]);FILTER('OINV E ORIN';'OINV E ORIN'[Estado Ano]="Previous"))

Consumo de clientes Activos Previous Year em Tons:

Consumo de clientes Activos Previous Year em Tons = CALCULATE(SUM('OINV E ORIN'[Quantidade Tons]);FILTER('OINV E ORIN';'OINV E ORIN'[Inactivo]<>"Y");FILTER('OINV E ORIN';'OINV E ORIN'[Estado Ano]="Previous"))

[Anonymous]

anyone?!

[tnolan]

I dont know of a way to dynamically default slicers. I am sure this is a feature that is coming. You can statically default slicers by selecting the months you want then saving. When you or someone else views the report, then those months that were highlighted when you saved will be the default selection on the filters.