Dynamic Date Calculation

[Anonymous]

I need assistance with date calculations for SIP investments in Power BI. Here's my scenario:

1. I want to calculate InvestmentA based on the earliest date of each month in my dataset.
2. I also want to calculate InvestmentA based on a custom date of the month (e.g., on the 15th of every month, there is an investment of ₹1000).

The challenge is that the Date column in my table is not continuous and skips non-trading days (weekends and public holidays).

Here's my initial approach for the custom date:
InvestmentA = IF(DAY([Date]) = 1, 1000, 0)

Additionally, I would like to allow the user to select this custom date using a slicer. I got What If table for date 1 to 31. But it doesn't work with columns. My goal is to see the growth of the portfolio over the years and investment date is user selected.

How can I adjust my DAX formula to account for the earliest date of the month and a user-selected custom date, considering the non-continuous nature of my Date column?

Date	Price
Wednesday, February 5, 2020	12,089.15
Tuesday, February 4, 2020	11,979.65
Monday, February 3, 2020	11,707.90
Saturday, February 1, 2020	11,661.85
Friday, January 31, 2020	11,962.10
Thursday, January 30, 2020	12,035.80
Wednesday, January 29, 2020	12,129.50
Tuesday, January 28, 2020	12,055.80
Monday, January 27, 2020	12,119.00
Friday, January 24, 2020	12,248.25
Thursday, January 23, 2020	12,180.35
Wednesday, January 22, 2020	12,106.90
Tuesday, January 21, 2020	12,169.85
Monday, January 20, 2020	12,224.55
Friday, January 17, 2020	12,352.35
Thursday, January 16, 2020	12,355.50
Wednesday, January 15, 2020	12,343.30
Tuesday, January 14, 2020	12,362.30
Monday, January 13, 2020	12,329.55
Friday, January 10, 2020	12,256.80
Thursday, January 9, 2020	12,215.90
Wednesday, January 8, 2020	12,025.35
Tuesday, January 7, 2020	12,052.95
Monday, January 6, 2020	11,993.05
Friday, January 3, 2020	12,226.65
Thursday, January 2, 2020	12,282.20
Wednesday, January 1, 2020	12,182.50
Tuesday, December 31, 2019	12,168.45
Monday, December 30, 2019	12,255.85
Friday, December 27, 2019	12,245.80
Thursday, December 26, 2019	12,126.55
Tuesday, December 24, 2019	12,214.55
Monday, December 23, 2019	12,262.75
Friday, December 20, 2019	12,271.80
Thursday, December 19, 2019	12,259.70
Wednesday, December 18, 2019	12,221.65
Tuesday, December 17, 2019	12,165.00
Monday, December 16, 2019	12,053.95
Friday, December 13, 2019	12,086.70
Thursday, December 12, 2019	11,971.80
Wednesday, December 11, 2019	11,910.15
Tuesday, December 10, 2019	11,856.80
Monday, December 9, 2019	11,937.50
Friday, December 6, 2019	11,921.50
Thursday, December 5, 2019	12,018.40
Wednesday, December 4, 2019	12,043.20
Tuesday, December 3, 2019	11,994.20
Monday, December 2, 2019	12,048.20
Friday, November 29, 2019	12,056.05
Thursday, November 28, 2019	12,151.15
Wednesday, November 27, 2019	12,100.70
Tuesday, November 26, 2019	12,037.70
Monday, November 25, 2019	12,073.75
Friday, November 22, 2019	11,914.40
Thursday, November 21, 2019	11,968.40
Wednesday, November 20, 2019	11,999.10
Tuesday, November 19, 2019	11,940.10
Monday, November 18, 2019	11,884.50
Friday, November 15, 2019	11,895.45
Thursday, November 14, 2019	11,872.10
Wednesday, November 13, 2019	11,840.45
Monday, November 11, 2019	11,913.45
Friday, November 8, 2019	11,908.15
Thursday, November 7, 2019	12,012.05
Wednesday, November 6, 2019	11,966.05
Tuesday, November 5, 2019	11,917.20
Monday, November 4, 2019	11,941.30
Friday, November 1, 2019	11,890.60
Thursday, October 31, 2019	11,877.45
Wednesday, October 30, 2019	11,844.10
Tuesday, October 29, 2019	11,786.85
Sunday, October 27, 2019	11,627.15
Friday, October 25, 2019	11,583.90
Thursday, October 24, 2019	11,582.60
Wednesday, October 23, 2019	11,604.10
Tuesday, October 22, 2019	11,588.35
Friday, October 18, 2019	11,661.85
Thursday, October 17, 2019	11,586.35
Wednesday, October 16, 2019	11,464.00
Tuesday, October 15, 2019	11,428.30
Monday, October 14, 2019	11,341.15
Friday, October 11, 2019	11,305.05
Thursday, October 10, 2019	11,234.55
Wednesday, October 9, 2019	11,313.30
Monday, October 7, 2019	11,126.40
Friday, October 4, 2019	11,174.75
Thursday, October 3, 2019	11,314.00
Tuesday, October 1, 2019	11,359.90
Monday, September 30, 2019	11,474.45
Friday, September 27, 2019	11,512.40
Thursday, September 26, 2019	11,571.20
Wednesday, September 25, 2019	11,440.20
Tuesday, September 24, 2019	11,588.20
Monday, September 23, 2019	11,600.20
Friday, September 20, 2019	11,274.20
Thursday, September 19, 2019	10,704.80
Wednesday, September 18, 2019	10,840.65
Tuesday, September 17, 2019	10,817.60
Monday, September 16, 2019	11,003.50
Friday, September 13, 2019	11,075.90
Thursday, September 12, 2019	10,982.80
Wednesday, September 11, 2019	11,035.70
Monday, September 9, 2019	11,003.05
Friday, September 6, 2019	10,946.20
Thursday, September 5, 2019	10,847.90
Wednesday, September 4, 2019	10,844.65
Tuesday, September 3, 2019	10,797.90
Friday, August 30, 2019	11,023.25
Thursday, August 29, 2019	10,948.30
Wednesday, August 28, 2019	11,046.10
Tuesday, August 27, 2019	11,105.35
Monday, August 26, 2019	11,057.85
Friday, August 23, 2019	10,829.35
Thursday, August 22, 2019	10,741.35
Wednesday, August 21, 2019	10,918.70
Tuesday, August 20, 2019	11,017.00
Monday, August 19, 2019	11,053.90
Friday, August 16, 2019	11,047.80
Wednesday, August 14, 2019	11,029.40
Tuesday, August 13, 2019	10,925.85
Friday, August 9, 2019	11,109.65
Thursday, August 8, 2019	11,032.45
Wednesday, August 7, 2019	10,855.50
Tuesday, August 6, 2019	10,948.25
Monday, August 5, 2019	10,862.60
Friday, August 2, 2019	10,997.35
Thursday, August 1, 2019	10,980.00
Wednesday, July 31, 2019	11,118.00
Tuesday, July 30, 2019	11,085.40
Monday, July 29, 2019	11,189.20
Friday, July 26, 2019	11,284.30
Thursday, July 25, 2019	11,252.15
Wednesday, July 24, 2019	11,271.30
Tuesday, July 23, 2019	11,331.05
Monday, July 22, 2019	11,346.20
Friday, July 19, 2019	11,419.25
Thursday, July 18, 2019	11,596.90
Wednesday, July 17, 2019	11,687.50
Tuesday, July 16, 2019	11,662.60
Monday, July 15, 2019	11,588.35
Friday, July 12, 2019	11,552.50
Thursday, July 11, 2019	11,582.90
Wednesday, July 10, 2019	11,498.90
Tuesday, July 9, 2019	11,555.90
Monday, July 8, 2019	11,558.60
Friday, July 5, 2019	11,811.15
Thursday, July 4, 2019	11,946.75
Wednesday, July 3, 2019	11,916.75
Tuesday, July 2, 2019	11,910.30
Monday, July 1, 2019	11,865.60

[Anonymous]

Hi @Anonymous ,

To adjust the DAX formula to account for the earliest date of the month and a user-selected custom date, try a formula similar to the following:

EarliestDateInvestment = 
VAR EarliestDate =
    CALCULATE(
        MIN('DateTable'[Date]),
        ALLEXCEPT('DateTable', 'DateTable'[Year], 'DateTable'[Month])
    )
RETURN
    IF(
        MIN('DateTable'[Date]) = EarliestDate,
        1000,
        0
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

My table conatins multi-year data. This DAX formula created a calculated column that invests 1000 on every trading day of January of the earliest year in the entire table which for me is the year 2000.