topic Re: calculate value on Lastdate if its blank in DAX Commands and Tips

calculate value on Lastdate if its blank

strombose007 — Thu, 29 Aug 2024 10:00:06 GMT

Hi all,

I am searching for a dax formula that can provides a value from last available date.

The Value for the date august and july is missing (only Company 2) and when I use this formula with visual filter on KPI it works:

Measure Last date cogs =

CALCULATE(

Sum(Tabelle1[Value]),

LASTDATE(Tabelle1[Date]))

But I need a formula that can filter. I tried the following formula:

Measure with filter =

CALCULATE(SUM('Tabelle1'[Value]),

FILTER('Tabelle1','Tabelle1'[KPI]="cogs"),

LASTDATE('Tabelle1'[Date]) )

The last date is august 24. But cogs have no available value in august and july but I want the last date that is available (june).

The problem is (I guess), Lastdate funktion identify the last date in the column date and the result is august 24 but for cogs is no data available.

Re: calculate value on Lastdate if its blank

Rupak_bi — Thu, 29 Aug 2024 10:13:05 GMT

Hi,

Below DAX should work.

CALCULATE(

Sum(Tabelle1[Value]),

ALLEXCEPT(Tabelle1, Tabelle1[Company],Tabelle1[Kpi]),Tabelle1[Date]=max(Tabelle1[Date])

)

If this works, plz accept as solution, else Plz let me know

Re: calculate value on Lastdate if its blank

strombose007 — Thu, 29 Aug 2024 10:28:14 GMT

Hi,

no it does not work, because I need the filter in the dax formula FILTER('Tabelle1','Tabelle1'[KPI]="cogs")

Re: calculate value on Lastdate if its blank

Rupak_bi — Thu, 29 Aug 2024 11:13:48 GMT

then modify the dax like this and try

CALCULATE(

Sum(Tabelle1[Value]),

ALLEXCEPT(Tabelle1, Tabelle1[Company]),Tabelle1[Date]=max(Tabelle1[Date]),'Tabelle1'[KPI]="cogs"

)

Re: calculate value on Lastdate if its blank

strombose007 — Thu, 29 Aug 2024 13:09:40 GMT

Unfortunately the result is blank. It should display in the visual (right visual) the last available value for the KPI (result 40,537). The current result for cogs in "august" and the "company 2" is blank, because the lastdate returns a value in august but the cogs has no value. I need the last available value of the KPI.

Here is the pbi file:

https://drive.google.com/file/d/1kV3XHYiPV36xw4ZB5V43bzyjWRGVwjS5/view?usp=drive_link

Re: calculate value on Lastdate if its blank

Anonymous — Fri, 30 Aug 2024 06:48:34 GMT

Hi strombose007 ,

You can try this measure.

MEASURE withFILTER = VAR _max_date = CALCULATE ( MAX ( 'Tabelle1'[Date] ), FILTER ( ALLSELECTED ( Tabelle1 ), 'Tabelle1'[KPI] = "cogs" ) ) RETURN CALCULATE ( SUM ( Tabelle1[Value] ), FILTER ( ALLSELECTED ( Tabelle1 ), 'Tabelle1'[Date] = _max_date && 'Tabelle1'[KPI] = "cogs" ) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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