topic Re: Calculate latest value in timeline - but total is still wrong in DAX Commands and Tips

Calculate latest value in timeline - but total is still wrong

jmuth — Sun, 11 Feb 2024 21:38:03 GMT

Hello everyone

I am fairly new to Power BI and DAX in particular is still causing me a lot of problems.

I want to create a KPI tile with a time series (last 12 months) and the current/last value in the header. I use a line chart for this.

If I display the value directly in the line chart, everything is fine. But in the header the same logic it is not working.

In a table, however, the correct value is displayed in the time series, but the unfiltered total is still calculated under "Total". This somehow seems to be the cause, but I am totally lost solving the problem:

GTK per Monat Σ Gesamt Ende Label = VAR MaxDate = CALCULATE ( MAX ( '02 Kalender'[Datum] ), ALLSELECTED () ) RETURN IF ( MAX ( '02 Kalender'[Datum] ) = MaxDate, [GTK per Monat Σ Gesamt] )

KPI HR GTK Header = VAR MaxDate = CALCULATE ( MAX ( '02 Kalender'[Datum] ), ALLSELECTED () ) RETURN //CONVERT(MaxDate, STRING) CONVERT(IF ( MAX ( '02 Kalender'[Datum] ) = MaxDate, [GTK per Monat Σ Gesamt] ), STRING)

I would be infinitely grateful if anyone has any advice.

Best regards

Jennifer

Re: Calculate latest value in timeline - but total is still wrong

talespin — Mon, 12 Feb 2024 01:07:30 GMT

hi jmuth

I used a Calendar table and TestTable with one to many relationship.

Latest Value =

VAR _MaxDt = CALCULATE( MAX(TestTbl5[Dt]), REMOVEFILTERS(TestTbl5) )

VAR _Val = CALCULATE( VALUES(TestTbl5[Value]), REMOVEFILTERS(TestTbl5), 'CALENDAR'[Date] = _MaxDt)

RETURN IF( ISBLANK(MAX(TestTbl5[Dt])), BLANK(),_Val)

Re: Calculate latest value in timeline - but total is still wrong

jmuth — Mon, 12 Feb 2024 16:34:39 GMT

Hi talespin ,

thank you for your reply. I didn't work out for my use case... I don't get it...

Nevertheless, I found a workaround - at least for my specific use case:

KPI HR GTK Header = CONVERT(LASTNONBLANKVALUE('02 Kalender (Betrachtungszeitraum)'[Datum], [GTK per Monat Σ Gesamt]), STRING)

Kind regards!