calculate value on Lastdate if its blank

[strombose007]

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.

[Anonymous · Accepted solution]

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.

[strombose007]

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

[strombose007]

Hi,

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

[Rupak_bi]

then modify the dax like this and try

CALCULATE(

        Sum(Tabelle1[Value]),

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

)

[Rupak_bi]

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