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 be done per a product highlighted by a 'code'.

 

The dates i have linked in another table.

 

The exmple below is taken as if it's the 11th February. The  'monthly value' column I need to create would need 0 in the date for all January execpt the last date there is a value for that Code. So for ABC that would be 17 & XYZ that would be 25. However they would have different last dates. The 31st Jan for ABC and 30th Jan for XYZ.

 

For February in the example if it part way through the month i require it to show the latest date. So for ABC and XYZ that would be both 10th February and ABC is 16 and XYZ is 55.

 

TABLE 1 TABLE 2
Date Daily ValueMonthly ValueCode
26/01/2020 170ABC
27/01/2020 150ABC
28/01/2020 140ABC
29/01/2020 150ABC
30/01/2020 160ABC
31/01/2020 1717ABC
01/02/2020 200ABC
02/02/2020 210ABC
03/02/2020 220ABC
04/02/2020 220ABC
05/02/2020 220ABC
06/02/2020 180ABC
07/02/2020 190ABC
08/02/2020 100ABC
09/02/2020 150ABC
10/02/2020 1616ABC
26/01/2020 170XYX
27/01/2020 150XYX
28/01/2020 140XYX
29/01/2020 200XYX
30/01/2020 2525XYX
01/02/2020 200XYX
02/02/2020 210XYX
03/02/2020 350XYX
04/02/2020 380XYX
05/02/2020 400XYX
06/02/2020 350XYX
07/02/2020 420XYX
08/02/2020 450XYX
09/02/2020 500XYX
10/02/2020 5555XYX

 

I would really appricate any help writing the measure for this as i'm pretty stuck trying to work it out.

 

Many Thanks

 

 

 

 

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

     

8 Replies

    • 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)))
       
       
       
      • 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