Latest Date when value>0

[kylee_anne]

What am I doing wrong?

Silo1 = CALCULATE(sum('Cement & Silo Data'[Value]),'Cement & Silo Data'[Description]="Silo 1 Actual (t)",'Cement & Silo Data'[Value]>0 &&'Cement & Silo Data'[Date]=MAX('Cement & Silo Data'[Date]))

From this data

Thanks.

[Anonymous · Accepted solution]

Source Data

Unpivot the value columns (including also the columns with 0 value)

Changing the data type of Attribute

Create a measure called latest date with max formula

Using a table visual

Now if you want to see only the latest date of actual then there are multiple ways
1. using slicer

2. Using filter pane

3. by updating the latest date formula

You can choose whichever is suitable for your case scenario.

If it solves your query then requesting you to accept the solution.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attahced pbix file.

Expected result Last Non Blank value: =
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            ALL ( 'Calendar'[Date] ),
            CALCULATE ( SUM ( 'Cement & Silo Data'[Value] ) <> 0 )
        ),
        'Calendar'[Date]
    )
RETURN
    CALCULATE (
        SUM ( 'Cement & Silo Data'[Value] ),
        KEEPFILTERS ( 'Calendar'[Date] = _lastnonblankdate ),
        KEEPFILTERS ( 'Description'[Description] = "Silo 1 Actual (t)" )
    )

[Irwan]

hello @kylee_anne 

from your dax, seems you are looking for sum of value.

if you want to get latest date, perhaps something like below (i assumed this is in measure form).

calculate(
   max('date'),

   filter(

      all('table'),

      'value'>0&&'description'="Silo 1 Actual(t)"))

Thank you.