DAX para conteo mes a mes segun fechas.

[paula_jimena]

Hola a todos, 

Estoy necesitando crear una medida que cuente la cantidad de proyectos vigentes por mes teniendo en cuenta la fecha de inicio y de finalización de cada proyecto. 
En la siguiente tabla se ve claramente: esta el ID del proyecto y las columnas de fecha inicio y fecha fin de cada proyecto. 

He probado muchísimas metricas pero no logro que ninguna cuente la cantidad de proyectos vigentes mes a mes. Haciendo el cálculo manualmente en enero 2022 hubo 6 proyectos en ese mes, febrero, marzo y abril se mantienen igual, en mayo hubo 9 proyectos vigentes y así sucesivamente teniendo en cuenta la fecha en la que inició y finalizó cada proyecto el resultado final sería: 

Haciendo un gráfico de linea tendriamos una linea continua que sube o baja segun la cantidad de proyectos. 
Las fórmulas que probé no me dejan hacer "crossfilter" y comento que no tengo "isblank" porque he probado con fórmulas que contienen estas expresiones y no lo he logrado. 

Dejo la spreadsheet para simplificar la tarea a quien pueda ayudarme, https://docs.google.com/spreadsheets/d/1-CFdDAawLf7AQj-BNJLtI2g7NTR5BuVgp3b_-EA0WB8/edit 

Gracias inmensas! 

[paula_jimena]

Disculpa que vuelva sobre el mismo problema pero todo iba bien con los datos del ejemplo pero al hacer la medida en el modelo real de datos no funciona bien.

Trae el mismo numero y lo replica para todos los meses. 

Como recomienda conectar las relaciones? seguramente replica el mismo numero porque no esta detectando la fecha inicio y y la fecha fin de cada proyecto. 

El modelo real de datos tiene una tabla calendario, no sé como hacer que funcione.

Dejo ejemplo de como es el resultado que veo.

Agradezco mucho la ayuda.

Saludos 🙂 

[paula_jimena]

Es perfecto, ¡muchas gracias! 😎

[v-zhangti · Accepted solution]

Hi, @paula_jimena 

You can try the following methods.
Create a new date table:

Table:

Date = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))

Column:

Month = MONTH([Date])

Measure:

Measure =
CALCULATE ( COUNT ( 'Table'[Project ID] ),
    FILTER ( ALL ( 'Table' ),
        [Start Date].[MonthNo] <= SELECTEDVALUE ( 'Date'[Month] )
            && [End Date].[MonthNo] >= SELECTEDVALUE ( 'Date'[Month] )
    )
)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paula_jimena]

Hola 🙂

Funciona en el ejemplo. No funciona en el modelo de datos real.

Disculpa que vuelva sobre el mismo problema pero todo iba bien con los datos del ejemplo pero al hacer la medida en el modelo real de datos no funciona bien.

Trae el mismo numero y lo replica para todos los meses. 

Como recomienda conectar las relaciones? seguramente replica el mismo numero porque no esta detectando la fecha inicio y y la fecha fin de cada proyecto. 

El modelo real de datos tiene una tabla calendario, no sé como hacer que funcione.

Dejo ejemplo de como es el resultado que veo.