Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamic Date Calculation

I need assistance with date calculations for SIP investments in Power BI. Here's my scenario: I want to calculate InvestmentA based on the earliest date of each month in my dataset. I also want to...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.