Forum Discussion

kylee_anne's avatar
kylee_anne
Icon for Helper II rankHelper II
1 year ago
Solved

Latest Date when value>0

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('Ce...
Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

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)" )
    )