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 ,

 

What's the relationship between your table1 and table2? How do you link these two tables? Would you please show us complete sample data for table1 and table2?

 

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

Table 1 is called: Date - Trade Calendar

Table 2 is called: Data

 

Date - Trade Calendar is linked by Date to Data: One to many

 

The my data poitns are called:

 

Date - Trade Calendar

Date - Trade

 

Data

Open Interest (lots)   

Unique Identifier

 

 

The dates are formatted as dates, open interest is a number and unique identifer is a text. Unfortuantlty i can't share the data. Thank you for your help

  • 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