topic Re: How can show last calculated value with a time intelligence measure? in DAX Commands and Tips

How can show last calculated value with a time intelligence measure?

Greyesbud — Tue, 21 Feb 2023 00:50:37 GMT

Hello:

I have 2 measures:

12M M³ Tratados = is the moving sum of the last 12 months

YoY% de 12 VT = is the yearly change in % of the previously described variable. For the last available month (January 2023) the calculation is ((64,157,367 / 60,751,835) -1) * 100 = 5.606%.

The problem:

I want to put a card on my report that shows the YoY% de 12 VT for the last month available, January in this case. But the card showed is the followed:

This number is the same that is showed at the bottom of the previous table and is the variation of 12M M³ Tratados against the value showed in December 2022: ((64,157,367 / 64,141,072) -1) * 100 = 0.0254%.

When I filter my report by dates, this card works fine, showing this calculation for each month, but the problem arise when no date is selected.

How can I show the by default the calculation for January 2023 (last month available)?

Here you can see how the measures are built:

YoY% de 12M VT = IF(STARTOFMONTH(Calendario[Fecha].[Date]) <= MAX(VARIABLES_OPERACIONALES[FECHA]),

IF(

ISFILTERED('Calendario'[Fecha].[Date]),

ERROR("La medida rápida de inteligencia de tiempo solo se puede agrupar o filtrar mediante la jerarquía de datos proporcionada por Power BI o por la columna de datos principal."),

VAR __PREV_YEAR = CALCULATE([12M VT], DATEADD('Calendario'[Fecha].[Date], -1, YEAR))

RETURN

DIVIDE([12M VT] - __PREV_YEAR, __PREV_YEAR)

)

12M VT = IF(STARTOFMONTH(Calendario[Fecha].[Date]) <= MAX(VARIABLES_OPERACIONALES[FECHA]),

CALCULATE([VT],DATESINPERIOD(Calendario[Fecha],ENDOFMONTH(Calendario[Fecha]),-12,MONTH)

))

Thank for your help

Re: How can show last calculated value with a time intelligence measure?

Anonymous — Thu, 23 Feb 2023 06:34:13 GMT

Hi Greyesbud ,

You can create a measure as below to replace the measure [YoY% de 12M VT] onto the visuals:

Measure = VAR _maxfdate = CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) ) VAR _mindate = DATE ( YEAR ( _maxfdate ) - 1, 1, 1 ) VAR _maxdate = DATE ( YEAR ( _maxfdate ) - 1, 12, 31 ) VAR _CYVT = CALCULATE ( [VT], DATESBETWEEN ( 'Calendario'[Fecha].[Date], DATE ( YEAR ( _maxfdate ), 1, 1 ), _maxfdate ) ) VAR _PYVT = CALCULATE ( [VT], DATESBETWEEN ( 'Calendario'[Fecha].[Date], _mindate, _maxdate ) ) RETURN IF ( ISFILTERED ( 'Calendario'[Fecha].[Date] ), [YoY% de 12M VT], DIVIDE ( ( _CYVT - _PYVT ), _PYVT ) )

If the above ones can't help you, could you please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: How can show last calculated value with a time intelligence measure?

Greyesbud — Thu, 23 Feb 2023 13:40:23 GMT

Hi yingyinr

Many thanks for your help.

Unfortunately, this measure doesn't work. I need the card to change the number when you select a month.

I found a solution replacing inside the original measure (YoY% de 12M VT), the measure that calculates the sum of the last 12 months ([12M VT]) by its content:

CALCULATE([VT],DATESINPERIOD(Calendario[Fecha],ENDOFMONTH(Calendario[Fecha]),-12,MONTH))

This new measure is the following:

YoY% 12M VT default = IF(HASONEVALUE(Calendario[Mes]),

[YoY% de 12M VT],

IF(HASONEVALUE(Calendario[Año]),

[YoY% de 12M VT],

(DIVIDE([12M VT], CALCULATE(CALCULATE([VT],DATESINPERIOD(Calendario[Fecha],ENDOFMONTH(Calendario[Fecha]),-12,MONTH)), FILTER(Calendario, Calendario[Fecha] = EOMONTH(Calendario[Última Fecha], -12))), 0) - 1)))

It seems DAX get confused when you incorporate too many measures with time intelligence inside them.

Again many thanks for your time,

Kind regards,

Gonzalo