Forum Discussion

kylee_anne's avatar
kylee_anne
Helper 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('Cement & Silo Data'[Date]))
 
From this data
 

 

Thanks.

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.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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)" )
    )

     

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.