Measure by buttoms

[migueldfr]

Hello

I would liekt o improve this measure in order to get when I click on "mes actual hasta hoy" the values for the current month, ignoring any master Calendar[month name] selected.

Total_Importe_Cobro_Dinamico = 
VAR SeleccionUsuario = SELECTEDVALUE('Seleccion_Rango_Fecha'[Opcion])  
VAR MesActual = MONTH(TODAY())  
VAR DiaActual = DAY(TODAY()) - 1  
VAR AnioActual = YEAR(TODAY())  
VAR MaxYear = MAX('MasterCalendar'[Year])  
VAR MesEnContexto = MAX('MasterCalendar'[Month Number])  

RETURN 
SWITCH(
    TRUE(),
    -- ✅ Opción 1: "Mes actual hasta hoy"
    SeleccionUsuario = "Mes actual hasta hoy",
    CALCULATE(
        SUM(CobrosAnx[importe_cobro]),
        'MasterCalendar'[IsCurrentMonth] = 1,  -- Filtrar por el mes actual
        'MasterCalendar'[Day] <= DiaActual    -- Limitar hasta el día actual
    ),

    -- ✅ Opción 2: "Rango de fechas" (Filtrar datos en el intervalo seleccionado)
    SeleccionUsuario = "Rango de fechas",
        CALCULATE(
            SUM(CobrosAnx[importe_cobro]),
            'MasterCalendar'[Date] >= MIN('MasterCalendar'[Date]) &&  -- Fecha mínima seleccionada
            'MasterCalendar'[Date] <= MAX('MasterCalendar'[Date]),    -- Fecha máxima seleccionada
            -- Aseguramos que la fecha de cualquier año seleccionado sea incluida
            'MasterCalendar'[Year] >= YEAR(MIN('MasterCalendar'[Date])) &&  
            'MasterCalendar'[Year] <= YEAR(MAX('MasterCalendar'[Date]))  
        ),
    -- ✅ Opción 3: "Por Meses" (mostrar todo o limitar si es el mes actual)
    SeleccionUsuario = "Por Meses",
    CALCULATE(
        SUM(CobrosAnx[importe_cobro]),
        'MasterCalendar'[Month Number] = MesEnContexto,  -- Mes en contexto
        IF(
            MesEnContexto = MesActual,  -- Si es el mes actual
            'MasterCalendar'[Day] <= DiaActual,  -- Limitar hasta el día actual
            TRUE()  -- Si es otro mes, mostrar todo
        )
    )
)

When I select a month name, the measure ofr "Mes actual hasta hoy " is showing up nothing, but I would like to keep static that filter in order to always get something for the user.

Thanks

[ARekha]

Hi @migueldfr ,

Thank you for reaching out to Microsoft Fabric Community.

I have created a sample dataset based on your requirements and developed a measure to address the issue. Below measure ensure the values are displayed for the current month, regardless of any other filters applied.

Also attached the pbix file for your reference.

Measure:

Total_Importe_Cobro_Dinamico =

VAR SeleccionUsuario = SELECTEDVALUE(Seleccion_Rango_Fecha[Column1])

VAR TodayDate = TODAY()

VAR FirstDayOfCurrentMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR Yesterday = TodayDate - 1

RETURN

SWITCH(

    TRUE(),

    SeleccionUsuario = "Mes actual hasta hoy",

    CALCULATE(

        SUM(CobrosAnx[importe_cobro]),

        FILTER(

            ALL('MasterCalendar'),

            'MasterCalendar'[Date] >= FirstDayOfCurrentMonth &&

            'MasterCalendar'[Date] <= Yesterday

        )

    ),

    BLANK()

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks & Regards,

Rekha,

Customer Support Team!!

[migueldfr]

Thanks for response.

I am asking what about the others options, what if I select "por meses", this does not show up on the measure

Thank you 

[ARekha]

Hi @migueldfr ,

Thank you for reaching out to Microsoft Fabric Community.

Since your requirement is to fetch values for the current month while ignoring any selection from the master Calendar[Month Name] when 'mes actual hasta hoy' is clicked, the measure below will return the same results. It returns current month values while ignoring the Calendar[Month Name] filter for 'mes actual hasta hoy' and behaves the same as your original measure when other values are selected.

Total_Importe_Cobro_Dinamico =

VAR SeleccionUsuario = SELECTEDVALUE(Seleccion_Rango_Fecha[Column1])

VAR TodayDate = TODAY()

VAR FirstDayOfCurrentMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR Yesterday = TodayDate - 1

VAR MesEnContexto = MAX('MasterCalendar'[Month Number])  

VAR DiaActual = DAY(TODAY()) - 1  

VAR MesActual = MONTH(TODAY())  

RETURN

SWITCH(

    TRUE(),

    SeleccionUsuario = "Mes actual hasta hoy",

    CALCULATE(

        SUM(CobrosAnx[importe_cobro]),

        FILTER(

            ALL('MasterCalendar'),

            'MasterCalendar'[Date] >= FirstDayOfCurrentMonth &&

            'MasterCalendar'[Date] <= Yesterday

        )

    ),

    SeleccionUsuario = "Rango de fechas",

        CALCULATE(

            SUM(CobrosAnx[importe_cobro]),

            'MasterCalendar'[Date] >= MIN('MasterCalendar'[Date]) &&  -- Fecha mínima seleccionada

            'MasterCalendar'[Date] <= MAX('MasterCalendar'[Date]),    -- Fecha máxima seleccionada

            -- Aseguramos que la fecha de cualquier año seleccionado sea incluida

            'MasterCalendar'[Year] >= YEAR(MIN('MasterCalendar'[Date])) &&  

            'MasterCalendar'[Year] <= YEAR(MAX('MasterCalendar'[Date]))  

        ),

    --  Opción 3: "Por Meses" (mostrar todo o limitar si es el mes actual)

    SeleccionUsuario = "Por Meses",

    CALCULATE(

        SUM(CobrosAnx[importe_cobro]),

        'MasterCalendar'[Month Number] = MesEnContexto,  -- Mes en contexto

        IF(

            MesEnContexto = MesActual,  -- Si es el mes actual

            'MasterCalendar'[Day] <= DiaActual,  -- Limitar hasta el día actual

            TRUE()  -- Si es otro mes, mostrar todo

        )

)

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks & Regards,

Rekha,

CustomerSupportTeam.

[migueldfr]

is true that I want to ingoring any selection that has been selected on month name box 

But in parallel I want it show me up the right value for the actual month 

and now is not working as I expected 

Thanks

[ARekha]

Hi @migueldfr ,

Could you please explain your follow up query in details to provide better solution.

Thanks & Regards,

Rekha.

[tamerj1]

Hi @migueldfr 

You need to keep in mind that the month name column is most probably sorted by the month number column, therefore you need to remove the filter from that column as well. In fact you can just remove the filter completely from the calendar table as follows 

CALCULATE (
        SUM ( CobrosAnx[importe_cobro] ),
        ALL ( 'MasterCalendar' ),
        'MasterCalendar'[IsCurrentMonth] = 1,
        'MasterCalendar'[Day] <= DiaActual
)

[migueldfr]

Hello 

It is doing a weird action 

and this is keeping for all the visualitation.

Thanks

[tamerj1]

@migueldfr 

What are you expecting to see in this chart?

[Sahir_Maharaj]

Hello @migueldfr,

Can you please try this approach:

Total_Importe_Cobro_Dinamico = 
VAR SeleccionUsuario = SELECTEDVALUE('Seleccion_Rango_Fecha'[Opcion])  
VAR MesActual = MONTH(TODAY())  
VAR DiaActual = DAY(TODAY()) - 1  
VAR AnioActual = YEAR(TODAY())  
VAR MaxYear = MAX('MasterCalendar'[Year])  
VAR MesEnContexto = MAX('MasterCalendar'[Month Number])  

RETURN 
SWITCH(
    TRUE(),
    -- Opción 1
    SeleccionUsuario = "Mes actual hasta hoy",
    CALCULATE(
        SUM(CobrosAnx[importe_cobro]),
        REMOVEFILTERS('MasterCalendar'[Month Name]), -- Remove any filter on the month
        'MasterCalendar'[IsCurrentMonth] = 1,  -- Filter by current month
        'MasterCalendar'[Day] <= DiaActual    -- Limit to the current day
    ),

    -- Opción 2
    SeleccionUsuario = "Rango de fechas",
        CALCULATE(
            SUM(CobrosAnx[importe_cobro]),
            'MasterCalendar'[Date] >= MIN('MasterCalendar'[Date]) &&  -- Min selected date
            'MasterCalendar'[Date] <= MAX('MasterCalendar'[Date]),    -- Max selected date
            'MasterCalendar'[Year] >= YEAR(MIN('MasterCalendar'[Date])) &&  
            'MasterCalendar'[Year] <= YEAR(MAX('MasterCalendar'[Date]))  
        ),

    -- Opción 3
    SeleccionUsuario = "Por Meses",
    CALCULATE(
        SUM(CobrosAnx[importe_cobro]),
        'MasterCalendar'[Month Number] = MesEnContexto,  -- Month in context
        IF(
            MesEnContexto = MesActual,  -- If it's the current month
            'MasterCalendar'[Day] <= DiaActual,  -- Limit to current day
            TRUE()  -- Show all if it's another month
        )
    )
)

Hope this helps!

[migueldfr]

Hello 

    CALCULATE(
        SUM(CobrosAnx[importe_cobro]),
        REMOVEFILTERS('MasterCalendar'[Month Name]), -- Remove any filter on the month
        'MasterCalendar'[IsCurrentMonth] = 1,  -- Filter by current month
        'MasterCalendar'[Day] <= DiaActual    -- Limit to the current day
    ),

This measure does not work as mentioned, when I select march in this case does not show up anything

I want to keep the filter ofr this month static

Thanks