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

Thank you for reviewing

  • 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