Last Date of the month

[baronraghu_3011]

Hi,

I have the below date and Price of a stock 

12-12-1980		0.128348
15-12-1980		0.12221
22-12-1980		0.132254
23-12-1980		0.137835
24-12-1980		0.145089
26-12-1980		0.158482
29-12-1980		0.160714
30-12-1980		0.157366
31-12-1980		0.152902
02-01-1981		0.154018
05-01-1981		0.151228
06-01-1981		0.144531
13-01-1981		0.136719
14-01-1981		0.136719
15-01-1981		0.139509
16-01-1981		0.138951
19-01-1981		0.146763
20-01-1981		0.142857
21-01-1981		0.145089
22-01-1981		0.146763
23-01-1981		0.146763
26-01-1981		0.144531
27-01-1981		0.143973
28-01-1981		0.138951
29-01-1981		0.133929
30-01-1981		0.127232
02-02-1981		0.11942
03-02-1981		0.123326
04-02-1981		0.12779
05-02-1981		0.12779
06-02-1981		0.128348
09-02-1981		0.122768
10-02-1981		0.121652
11-02-1981		0.118304
12-02-1981		0.117188
13-02-1981		0.114955
17-02-1981		0.116629
18-02-1981		0.121652
19-02-1981		0.114955
20-02-1981		0.108817
23-02-1981		0.109933
24-02-1981		0.107143
25-02-1981		0.112723
26-02-1981		0.114397
27-02-1981		0.118304
02-03-1981		0.118862
03-03-1981		0.117746
04-03-1981		0.116629
05-03-1981		0.116071
06-03-1981		0.115513
09-03-1981		0.106027
10-03-1981		0.101004
11-03-1981		0.097656
23-03-1981		0.11942
24-03-1981		0.11942
25-03-1981		0.117746
26-03-1981		0.114955
27-03-1981		0.111049
30-03-1981		0.110491
31-03-1981		0.110491
01-04-1981		0.108817
02-04-1981		0.117746
03-04-1981		0.118304
06-04-1981		0.116629
07-04-1981		0.115513
08-04-1981		0.120536
09-04-1981		0.122768
10-04-1981		0.124442
13-04-1981		0.124442
14-04-1981		0.124442
24-04-1981		0.13058
27-04-1981		0.128906
28-04-1981		0.126674
29-04-1981		0.125
30-04-1981		0.126674
01-05-1981		0.126674
04-05-1981		0.126674
05-05-1981		0.126116
26-05-1981		0.140067
27-05-1981		0.147321
28-05-1981		0.147321
29-05-1981		0.147879
01-06-1981		0.147879
02-06-1981		0.141183
03-06-1981		0.140625
04-06-1981		0.143415
05-06-1981		0.141741
08-06-1981		0.136719
09-06-1981		0.138951
10-06-1981		0.140625
11-06-1981		0.146763
24-06-1981		0.130022
25-06-1981		0.131696
26-06-1981		0.131138
29-06-1981		0.126674
30-06-1981		0.116629
01-07-1981		0.115513
02-07-1981		0.114955
06-07-1981		0.112165
07-07-1981		0.112165
08-07-1981		0.116629
09-07-1981		0.108259
10-07-1981		0.099888
13-07-1981		0.101563
14-07-1981		0.106027
15-07-1981		0.108817
16-07-1981		0.111607
17-07-1981		0.115513
20-07-1981		0.108259
21-07-1981		0.107701
22-07-1981		0.102121

I am looking to find the price of the stock on the last date of each month. The last date may not be End of month as it could be a holiday or a weekend. The output should be like the below table

31-12-1980		0.152902
29-01-1981		0.133929
27-02-1981		0.118304
31-03-1981		0.110491
30-04-1981		0.126674

[bolfri]

Hi,

I think that what you want to achive is has a max date in current month in your data.

Step 1. Add new column

Max date in month =

var row_date = [Date]

var max_date = CALCULATE(MAX([Date]);ALL('Sample');DATEDIFF('Sample'[Date];row_date;MONTH)=0)

return row_date=max_date

Step 1. Filter true to see results

You can use this column in your measures 🙂

[FreemanZ]

hi @baronraghu_3011 

you can add a tag column like this:

Tag =

VAR _monthend = EOMONTH([Date],0)

VAR _date =

MAXX(

    FILTER(

        TableName,

        TableName[Date]<=_monthend

    ),

TableName[Date]

)

RETURN

IF([date] =_date, "Yes", "No" )

then you filter with the Tag column, like this:

[baronraghu_3011]

Hi,

Looking at the screenshot you have shared, it looks the code may return the sum of price however, i want the closing day price. Also I tried your code and this is what i am getting

The last trading date should be the closing date 

[Mahesh0016 · Accepted solution]

@baronraghu_3011 

LastDatofMonth =

 CALCULATE([Total ListPrice],ENDOFMONTH(DimDate[Date]))

i hope this post helps.

[baronraghu_3011]

HI... The end of the month may not be the same as trading day on account of public holiday or weekend.

[AbbasG]

@baronraghu_3011 refer to the below,

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=f9c5dcc0-...

[baronraghu_3011]

HI.. Thanks for sharing the link. The solution offered in the link only picks up the end of month dates. However in my table the end of month date may not be a trading day on account of being a weekend or  a public holiday