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.
So I'm trying to create a table that has a YTD and MTD column, based on a measure that filters from a report spec.
The table I'm trying to create looks like this (apologies for the Spanish):
Where Cifra de Negocio is defined as a measure:
Cifra de Negocio =
CALCULATE ( SUM ( 'Transacciones Control de Gestion'[Valor Contable] ), FILTER ( 'Informes', 'Informes'[Nivel 1] = "CIFRA DE NEGOCIO" ) )
And Cifra de Negocio Año Anterior is defined as the following measure:
Cifra De Negocio Año Anterior = CALCULATE ( [Cifra De Negocio], SAMEPERIODLASTYEAR ( Fechas[Fecha] ) )
Transacciones Control de Gestión is a filtered table that contains all my transactions. Informes contains a report specification, which maps to a number of different account numbers that are related to Transacciones Control de Gestión.
In the table, Mes and Año are the spanish versions of MTD and YTD.
So I try to rewrite Cifra de Negocio as:
Cifra De Negocio = CALCULATE ( SUM ( 'Transacciones Control de Gestion'[Valor Contable] ), FILTER ( 'Informes', 'Informes'[Nivel 1] = "CIFRA DE NEGOCIO" ), IF ( ISFILTERED ( 'Periodos en Curso'[Periodo] ), SWITCH ( VALUES ( 'Periodos en Curso'[Periodo] ), "Mes", DATESMTD ( Fechas[Fecha] ), "Año", DATESYTD ( Fechas[Fecha] ), ALLSELECTED ( Fechas[Fecha] ) ), ALLSELECTED ( Fechas[Fecha] ) ) )
But I get the error "A function 'DATESYTD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
I've eventually ended up replacing that measure with:
Cifra De Negocio = IF ( ISFILTERED ( 'Periodos en Curso'[Periodo] ), SWITCH ( VALUES ( 'Periodos en Curso'[Periodo] ), "Mes", TOTALMTD ( SUM ( 'Transacciones Control de Gestion'[Valor Contable] ), Fechas[Fecha], FILTER ( 'Informes', 'Informes'[Nivel 1] = "CIFRA DE NEGOCIO" ) ), "Año", TOTALYTD ( SUM ( 'Transacciones Control de Gestion'[Valor Contable] ), Fechas[Fecha], FILTER ( 'Informes', 'Informes'[Nivel 1] = "CIFRA DE NEGOCIO" ) ) ), CALCULATE ( SUM ( 'Transacciones Control de Gestion'[Valor Contable] ), ALLSELECTED ( Fechas[Fecha] ), FILTER ( 'Informes', 'Informes'[Nivel 1] = "CIFRA DE NEGOCIO" ) ) )
Which seems, at best, a very long-winded way of doing things.
My question is twofold:
Hi @Moof
In CALCULATE() function, the parameter can be a True/False expression. But you need to put a filted table context. For your requirement, you may try to use VAR Function to optimize your formula. Below is the article for your reference.
https://www.sqlbi.com/articles/variables-in-dax/
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |