Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
KarlaBMS93
Helper I
Helper I

Sumar una Columna filtrada por año y mes

Hola chicos, necesito de su ayuda.

 

Tengo una duda quiero sumar mi columna PSS pero con la condición que la columna CLOSED DATE fuera mayor igual al inicio del mes o que sea Blanco, pero cuando lo quiero filtrar por Año y mes no aparece nada, de hecho cuando filtro no aparece nada y no sé porque, no sé si es porque la relación es secundaria

Esta es mi medida

SUMA_PSS = CALCULATE(

    SUM(CAR_DETAILS[PSS]),

    USERELATIONSHIP(CAR_DETAILS[CLOSED DATE],Calendario[Date]),

    FILTER(

        CAR_DETAILS,

        CAR_DETAILS[CLOSED DATE]>=MIN(Calendario[Date]) || CAR_DETAILS[CLOSED DATE]=BLANK()

    )

)

Aqui les comparto mi archivo: https://www.dropbox.com/s/4tw2c3wsyfobmts/DEPARTAMENTOS.pbix?dl=0

 

 

tabla sin filtrartabla sin filtrartabla con el filtrotabla con el filtro

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

Because you are using USERELATIONSHIP to connect Car_Details table to Calendario table, the filter to look for [Closed Date] >= Calendario[Date] will return ONLY values when your closed date is equal to calendar date.

Looking at your sample data, your closed date and submit date are in Date/Time format. So, you need to use Power Query to extract just a Date Key : https://excelwithallison.blogspot.com/2020/10/datekey-how-to-relate-your-date-data.html

This will still only give you values that are == to the date, so you may also want to remove the USERELATIONSHIP part of your formula, since you're doing the filter with the FILTER function.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

1 REPLY 1
AllisonKennedy
Super User
Super User

Because you are using USERELATIONSHIP to connect Car_Details table to Calendario table, the filter to look for [Closed Date] >= Calendario[Date] will return ONLY values when your closed date is equal to calendar date.

Looking at your sample data, your closed date and submit date are in Date/Time format. So, you need to use Power Query to extract just a Date Key : https://excelwithallison.blogspot.com/2020/10/datekey-how-to-relate-your-date-data.html

This will still only give you values that are == to the date, so you may also want to remove the USERELATIONSHIP part of your formula, since you're doing the filter with the FILTER function.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.