Forum Discussion

CM_Mills's avatar
CM_Mills
Helper II
5 years ago
Solved

Value Based on Max Date Per Month

Hello,   I'm looking to create a measure to create a new monthly value but only on the latest date or the last date of a month. The rest of the dates I require the value to be 0.   This needs to ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi CM_Mills ,

     

    Do you mean there is no data of 3/28 in your data table? If so, try to use the following measure:

     

    Open Interest - Lots (Monthly) =
IF (
    MAX ( 'Date - Trade Calendar'[Date - Trade] ) IN VALUES ( Data[Date] ),
    IF (
        MAX ( 'Date - Trade Calendar'[Date - Trade] )
            = CALCULATE (
                LASTNONBLANK ( Data[Date - Trade], SUM ( Data[Open Interest - Lots] ) ),
                FILTER (
                    ALL ( Data ),
                    Data[Unique Identifier] = MAX ( Data[Unique Identifier] )
                        && MONTH ( Data[Date - Trade] ) = MONTH ( MAX ( Data[Date - Trade] ) )
                )
            ),
        CALCULATE (
            LASTNONBLANKVALUE ( 'Data'[Date - Trade], SUM ( Data[Open Interest - Lots] ) ),
            FILTER (
                ALL ( Data ),
                Data[Unique Identifier] = MAX ( Data[Unique Identifier] )
                    && MONTH ( Data[Date - Trade] ) = MONTH ( MAX ( Data[Date - Trade] ) )
            )
        ),
        0
    ),
    BLANK ()
)

     

     

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

     

    Best Regards,

    Dedmon Dai

     

amitchandak's avatar
amitchandak
Super User
5 years ago

CM_Mills , Try a measure like

calculate(lastnonblankvalue(Table[date], sum(Table[Value])), filter(allselected(Table), eomonth(Table[date],0) = eomonth(max(Table[date]),0)))

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

CM_Mills's avatar
CM_Mills
Helper II
5 years ago

Would that solution take into account the code?

 

I have tried to enter the measure but I'm getting the error: 

 

A single value for column 'Date - Trade' in table 'Date - Trade Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Please see my measure:

 

Measure = calculate(lastnonblankvalue('Date - Trade Calendar'[Date - Trade], sum(Data[Open Interest - Lots])), filter(allselected(Data), eomonth('Date - Trade Calendar'[Date - Trade],0) = eomonth(max('Date - Trade Calendar'[Date - Trade]),0)))