Value Based on Max Date Per Month

[CM_Mills]

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 Value	Monthly Value	Code
26/01/2020		17	0	ABC
27/01/2020		15	0	ABC
28/01/2020		14	0	ABC
29/01/2020		15	0	ABC
30/01/2020		16	0	ABC
31/01/2020		17	17	ABC
01/02/2020		20	0	ABC
02/02/2020		21	0	ABC
03/02/2020		22	0	ABC
04/02/2020		22	0	ABC
05/02/2020		22	0	ABC
06/02/2020		18	0	ABC
07/02/2020		19	0	ABC
08/02/2020		10	0	ABC
09/02/2020		15	0	ABC
10/02/2020		16	16	ABC
26/01/2020		17	0	XYX
27/01/2020		15	0	XYX
28/01/2020		14	0	XYX
29/01/2020		20	0	XYX
30/01/2020		25	25	XYX
01/02/2020		20	0	XYX
02/02/2020		21	0	XYX
03/02/2020		35	0	XYX
04/02/2020		38	0	XYX
05/02/2020		40	0	XYX
06/02/2020		35	0	XYX
07/02/2020		42	0	XYX
08/02/2020		45	0	XYX
09/02/2020		50	0	XYX
10/02/2020		55	55	XYX

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

Many Thanks

[amitchandak]

@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]

Thank you for reviewing

[v-deddai1-msft]

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]

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]

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/EeEjelqlkJVEp39uEd...

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]

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 · Accepted solution]

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

[CM_Mills]

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