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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.