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

     

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi CM_Mills ,

 

Would you please try the following measure:

 

 

Monthly Value = IF(MAX('Date'[Date])= CALCULATE(LASTNONBLANK(Data[Date],SUM(Data[Daily Value])),FILTER(ALL(Data),Data[Code] = MAX(Data[Code])&&MONTH(Data[Date])= MONTH(MAX(Data[Date])))),CALCULATE(LASTNONBLANKVALUE('Data'[Date],SUM(Data[Daily Value])),FILTER(ALL(Data),Data[Code] = MAX(Data[Code])&&MONTH(Data[Date])= MONTH(MAX(Data[Date])))),0)

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeEjelqlkJVEp39uEdIeHk8BBV2YCzD1fcPoEImln8JaaA?e=Ocxdr9

 

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

 

 

CM_Mills's avatar
CM_Mills
Helper II
5 years ago

Hi,

 

Thank you this, it's nearly there. The formula has now made extra rows show where there is no data. For example previously there was no 28th March shown but now in the table it shoes that date with BLANK value in the orginial data point (Open Interest - Lots) and a 0 in the new measure.

It would work better if the measure didn't add a value to these dates so they don't show.

 

Any help provided would be truely appricated:

Open Interest - Lots (Monthly) = (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))

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    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