Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
baronraghu_3011
Frequent Visitor

Last Date of the month

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
1 ACCEPTED SOLUTION
Mahesh0016
Super User
Super User

LastDatofMonth =
 CALCULATE([Total ListPrice],ENDOFMONTH(DimDate[Date]))
 
i hope this post helps.

View solution in original post

7 REPLIES 7
bolfri
Super User
Super User

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 🙂

bolfri_0-1671619589559.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




FreemanZ
Super User
Super User

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:
FreemanZ_0-1671619467081.png

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

baronraghu_3011_0-1671620200391.png

The last trading date should be the closing date 

Mahesh0016
Super User
Super User

LastDatofMonth =
 CALCULATE([Total ListPrice],ENDOFMONTH(DimDate[Date]))
 
i hope this post helps.

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.