Show measure values till the month selected

[ArquimedesP]

Hi! I'm having real bad time to cumplish this. I have two tables,  VW_Fecha and VW_Indicadores_Gestion. Both are disconected from each other. Then I have this measures:

1) KPI_CR_Ano = 

                VAR Anio  =

                VAR Anio_Hoy = YEAR(TODAY())

                RETURN

                 IF(NOT(ISFILTERED(VW_Fecha[Anio_Nu])),Anio_Hoy,SELECTEDVALUE(VW_Fecha[Anio_Nu]))
2) KPI_CR_Mes =

                VAR Mes_Hoy= MONTH(TODAY())

                RETURN

                COALESCE(VALUE(SELECTEDVALUE(VW_Fecha[Mes_Nu])),Mes_Hoy)

3) KPI_CR_MOV_SA =

                 VAR Ano=[KPI_CR_Ano]

                 VAR Mes=[KPI_CR_Mes]

                 VAR C = 1

                 VAR IF_SA=CALCULATE(SUM(VW_Indicadores_Gestion[Movimiento_Neto]),VW_Indicadores_Gestion[Grupo_Padre]      ="Ingresos Financieros",VW_Indicadores_Gestion[Agrupador_Cuenta_De]<>"Ingresos Financieros AxI",VW_Indicadores_Gestion[Anio_Valor_Contable]=Ano,VW_Indicadores_Gestion[Mes_Valor_Contable]=Mes)
                  RETURN
                  IF_SA
I show the results in a Bar chart visual where in X's axis I have [Mes-Anio] and Y's axis I have [KPI_CR_MOV_SA]. I also have Year selector (fromVW_Fecha) and Month selector (fromVW_Fecha). 

My issue is:  I want to show in the Bar chart visual the result of the measure till the month selected. How can I accomplish this only with this two table (I don't want to use calendar date table)?

Thanks! 

[parry2k]

@ArquimedesP have you tried the measure I provided? It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[ArquimedesP]

I used DataNinja777 formula as a reference and modified it and it worked. I also used a calendar table so I can use it as a Year-Month filter. Thanks to all. 

[DataNinja777 · Accepted solution]

Hi @ArquimedesP ,

In order to sever the filtering coming from the slicer and from your Y-axis on your bar chart, you need to create separate disconnected tables for the slicer and Y-axis for the calendar data.  

Then, you can write a measure like below:

Movement for Chart = 
VAR SelectedYear =
    SELECTEDVALUE( YearSelector[Anio_Valor_Contable] )

VAR SelectedMonthCutoff =
    SELECTEDVALUE( MonthSelector[MonthNumberSel] )

-- The bar's own Year and Month coming from the axis table VW_Fecha
VAR ThisYear  = MAX( VW_Fecha[Anio_Nu] )
VAR ThisMonth = MAX( VW_Fecha[Mes_Nu] )

-- Build a YYYYMM key for cutoff and this bar
VAR CutoffKey = SelectedYear * 100 + SelectedMonthCutoff
VAR ThisKey   = ThisYear     * 100 + ThisMonth

RETURN
IF(
    NOT ISBLANK(SelectedYear)
    && NOT ISBLANK(SelectedMonthCutoff)
    && ThisKey <= CutoffKey,

    CALCULATE(
        SUM( VW_Indicadores_Gestion[Movimiento_Neto] ),
        FILTER(
            VW_Indicadores_Gestion,
            VW_Indicadores_Gestion[Anio_Valor_Contable] = ThisYear
            && VW_Indicadores_Gestion[Mes_Valor_Contable] = ThisMonth
            && VW_Indicadores_Gestion[Grupo_Padre] = "Ingresos Financieros"
            && VW_Indicadores_Gestion[Agrupador_Cuenta_De] <> "Ingresos Financieros AxI"
        )
    )
)

And put that measure and disconnected dimension tables fields in the visual like below:

I have attached an example pbix file for your reference.

Best regards,

[ArquimedesP]

Thanks but didn't work! The measure already calculate the value for each month. Maybe I need a new approach and use calendar table but them I'll have to recalculate everything!😪

[parry2k]

@ArquimedesP Not sure why you don't want to use the calendar table. Anyhow, this is what I will try:

//add new column in VW_Indicadores_Gestion table
Date Column = EOMONTH ( DATE ( VW_Indicadores_Gestion [Year Column], VW_Indicadores_Gestion [Month Column], 1 ), 0 )

KPI_CR_MOV_SA =
VAR Ano = [KPI_CR_Ano]
VAR Mes = [KPI_CR_Mes]
VAR MyDate =
    EOMONTH ( DATE ( [Ano], [Mes], 1 ), 0 )
VAR C = 1
VAR IF_SA =
    CALCULATE (
        SUM ( VW_Indicadores_Gestion[Movimiento_Neto] ),
        VW_Indicadores_Gestion[Grupo_Padre] = "Ingresos Financieros",
        VW_Indicadores_Gestion[Agrupador_Cuenta_De] <> "Ingresos Financieros AxI",
        VW_Indicadores_Gestion[New Date Column] <= MyDate
    )
RETURN
    IF_SA