Filter conteniendo una medida de tiempo

[Anonymous]

Buen dia estimados, tengo un problema al usar Filter conteniendo una medida:

CN Tabla Cierre = FILTRO (TODOS ('bi Fact_Summary'), 'bi Fact_Summary' [Cierre_Periodo] = [T_Periodo_Anterior])

y me manda este error:
La expresión hace referencia a varias columnas. No se pueden convertir varias columnas a un valor escalar.

[T_Periodo_Anterior] es una medida que me entrega este dato "202107" (año y mes).

Mi intencion es sumar y contar las facturas del cierre anterior.
Cada cierre es una foto de toda la información de Octubre 2020 a la fecha. Esto quiere decir que el periodo 202106 contiene la informacion de la facturacion desde 01/10/2020 al 30/06/2021, el periodo 202107 contiene la facturacion del 01/10/2020 al 31/07/2021 y asi sucesivamente. Estas fotos guardan el estatus de las órdenes antes de ser facturas.
Y cada mes tiene el numero de factura y su fecha de factura.

Ejemplo:
Si estoy en el periodo 202107 se debe calcular la facturacion de Junio ​​(202106) desde el 01/06/2021 al 30/06/2021
Si estoy en el periodo 202108 se debe calcular la facturacion de Julio (202107) desde el 01 / 07/2021 al 31/07/2021

Mi intencion fue crear una medida que contenga el periodo anterior y de alli hacer las suma de facturacion y cantidad de facturas.

Espero de su gran apoyo.

Slds.

[Anonymous]

Hola Paul

En este enlace estan los archivos:

https://drive.google.com/drive/folders/1XHb7LQQ-xCIZzjqXuTW4W7oEEocTZ_Lq?usp=sharing

Gracias por tu ayuda.

[PaulDBrown]

Gracias por los archivos. Revisando tu modelo de datos & medidas, te comento:

En algunas medidas usas funciones de Time Intelligence (DATESBETWEEN...) Debes tener cuidado, ya que para usar funciones de Time Intelligence debes usar una tabla calendario que incluya fechas consecutivas que cubran todo el periodo incluido en el modelo de datos.

Teniendo en cuenta esto, además del modelo de datos, he creado dos tablas de consulta nuevas: 

1) D Periodo Venta que cubre los periodos de venta en el modelo

Establecemos una relación 1:N entre los campos de periodo de esta tabla y la BD 

2) Dim Calendario FF (para las fechas factura)

La columna "Index" en esta tabla se corresponde con los valores por periodo de la tabla Dim Periodo Venta.

Establecemos una relación 1:N entre los campos de Date de esta tabla y la de Fecha Factura en BD 

El modelo queda así:

Creamos las medidas siguientes:

Fact Mes =
CALCULATE (
    [Total Factura],
    FILTER (
        'Dim Calendario FF',
        'Dim Calendario FF'[MesNum] = SELECTEDVALUE ( 'Dim Periodo Venta'[DNumMes] )
            && 'Dim Calendario FF'[Periodo] = SELECTEDVALUE ( 'Dim Periodo Venta'[DPeriodo] )
    )
)

Fact Mes Anterior =
VAR Mes =
    IF (
        SELECTEDVALUE ( 'Dim Periodo Venta'[DNumMes] ) = 1,
        12,
        SELECTEDVALUE ( 'Dim Periodo Venta'[DNumMes] ) - 1
    )
RETURN
    CALCULATE (
        [Total Factura],
        FILTER (
            'Dim Calendario FF',
            'Dim Calendario FF'[MesNum] = Mes
                && 'Dim Calendario FF'[Index]
                    = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1
        )
    )

TotVenta Mes Anterior =
CALCULATE (
    [Total Factura],
    FILTER (
        ALL ( 'Dim Periodo Venta' ),
        'Dim Periodo Venta'[Index]
            = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1
    )
)

Y este es el resultado:

Te adjunto el archivo PBIX

[Anonymous]

Hola Paul,

Muchas gracias por el tiempo que tomas en ayudar, aplique la solucion a mi modelo pero no logro obtener el resultado de Fac Mes Anterior.

Obtengo la suma de todos los meses pero del periodo actual.

[PaulDBrown]

Puedes por favor poner imágenes de las dos tablas de búsqueda nuevas que has creado?

y también una del modelo de datos 

[Anonymous]

Estas son las imagenes, tambien los subi al Drive:

Modelo de Datos:

Relacion con Calendario FF por fecha de factura y Date

Relacion con tabla Periodo Venta por DPeriodo y Cierre_Periodo

Tabla Periodo Venta:

Tabla Calendario FF:

Resultado, DAX TT Facturacion Mes Ant

[Anonymous]

Y este es con la formula completa, con la variable Mes, obtengo el mismo resultado.

[PaulDBrown]

Si quieres, prueba esta medida de comprobación. A mi me da el mismo resultado que la facturación del mes anterior:

TEST =
CALCULATE (
    [Fact Mes],
    FILTER (
        ALL ( 'Dim Periodo Venta' ),
        'Dim Periodo Venta'[Index]
            = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1
    )
)

Lo único asegúrate que la facturación del mes sea correcta. Para comprobarlo, crea una tabla aparte con los campos de la tabla Calendario FF con la suma de facturación 

[PaulDBrown]

la medida no es correcta. Fíjate en la que puse (en particular la expresión del filtro - tabla y campos)

TotVenta Mes Anterior =
CALCULATE (
    [Total Factura],
    FILTER (
        ALL ( 'Dim Periodo Venta' ),
        'Dim Periodo Venta'[Index]
            = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1
    )
)

[Anonymous]

En este DAX tengo el problema:

Fact Mes Anterior =
VAR Mes = IF ( SELECTEDVALUE ( 'Dim Periodo Venta'[DNumMes] ) = 1, 12,
SELECTEDVALUE ( 'Dim Periodo Venta'[DNumMes] ) - 1 )
RETURN
CALCULATE ( [Total Factura],
FILTER ( 'Dim Calendario FF',
'Dim Calendario FF'[MesNum] = Mes 
&& 'Dim Calendario FF'[Index] = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1

[PaulDBrown]

Pero esta no es la medida que buscas no? La que falla es la que calcula la suma del periodo de venta anterior. Mira mi mensaje previo. La medida que necesitas es:

TotVenta Mes Anterior =
CALCULATE (
    [Total Factura],
    FILTER (
        ALL ( 'Dim Periodo Venta' ),
        'Dim Periodo Venta'[Index]
            = SELECTEDVALUE ( 'Dim Periodo Venta'[Index] ) - 1
    )
)

no se filtra la tabla calendario, sino la tabla periodo venta (y ojo al ALL)

[Anonymous]

Si, TotVenta Mes Anterior funciona perfecto, que es la facturacion total por el periodo, pero la Facturacion del mes Anterior que es solo la facturacion del mes anterior no esta saliendo muy bien.

[PaulDBrown]

Me pones el código de la facturación del mes?

[Anonymous]

La facturacion del mes es:

TT Facturacion Mes = CALCULATE([Total Amount], FILTER('DM Calendario FF', 'DM Calendario FF'[MesNum] = SELECTEDVALUE('DM Periodo Venta'[DNumMes]) && 'DM Calendario FF'[Periodo] = SELECTEDVALUE('DM Periodo Venta'[DPeriodo])))

Y la que calcula el total es:

Total Amount = SUM('bi Fact_Summary_BI'[Net_Amount])

[PaulDBrown]

Puedes crear un visual de tabla con campos de calendario FF y suma de facturación?

[Anonymous]

Claro, este es el codigo:

TT Facturacion Mes Ant =
VAR Mes = IF(SELECTEDVALUE('DM Periodo Venta'[DNumMes]) = 1, 12, SELECTEDVALUE('DM Periodo Venta'[DNumMes])-1)
RETURN
CALCULATE([Total Amount], FILTER( 'DM Calendario FF', 'DM Calendario FF'[MesNum] = Mes && 'DM Calendario FF'[Index] = SELECTEDVALUE('DM Periodo Venta'[Index])-1))

[PaulDBrown]

No, me refiero a la calculación del mes (no del mes anterior). La puedes además comprobar creando un visual de tabla con los campos de la tabla Calendario ff y la suma de facturación 

[Anonymous]

Hola Paul,

Use la formula que enviaste pero no obtuve el resultado esperado.

Quiero enviar mi archivo pbix y un excel y no se como hacerlo.

Spoiler

Spoiler

[PaulDBrown]

Para compartir un archivo, usa un servicio tipo Google Drive, Dropbox, One Drive...

[PaulDBrown]

el cálculo de CN Tabla Cierre  genera una tabla de datos, y eso no es posible con una medida. Para convertirlo a una medida puedes usar por ejemplo:

CN Tabla Cierre =COUNTROWS ( FILTRO (TODOS ('bi Fact_Summary'), 'bi Fact_Summary' [Cierre_Periodo] = [T_Periodo_Anterior]))

y usar la medida como filtro para un visual

tambien puedes crear una medida con la suma de facturación tipo:

Facturación periodo anterior = CALCULATE([Suma facturación], FILTRO (TODOS ('bi Fact_Summary'), 'bi Fact_Summary' [Cierre_Periodo] = [T_Periodo_Anterior]))

donde [Suma facturación] es una simple suma de la columna de facturación (tipo SUM(tabla[facturación]).

disculpa si pongo funciones en inglés pero desconozco la equivalencia en DAX en español.