Forum Discussion

franorio's avatar
franorio
Icon for Helper III rankHelper III
9 years ago
Solved

Time intelligence add months to a given date

Hello everybody,   got a question, is it possible to add/sum time to a given date? Got my report working with a Dim Calendar Table. Long story short,  I need always to have added 3 months to each ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi franorio,

    Please try the function Dateadd function, here is the link: https://msdn.microsoft.com/en-us/library/ee634905.aspx.

    You can also use the formula to create a calculated column.

    Column = DATE(YEAR('Test1'[Date]),MONTH('Test1'[Date])+3,DAY('Test1'[Date]))


    I use my sample data and get the expected result, please see the following screenhot. Then you can use the new date to create visual.



    Thanks,
    Angelia

Anonymous's avatar
Anonymous
Not applicable
6 years ago

franorio 
I am finding that DAX may not be the solution for this one.  In my case, I used Power Query to edit the table (adding 3 months after the date) before bringing it into the model.

 

Fore example, in power query you can create a custom column stating:

 

Add 3 months to a given fact table date column

=
Date.AddMonths( [Snapshot_Date],3)

 

I have found using DAX formulas may pose ambiguity when adding months to dates where the 'day number' is > 28.

for example:

8/31/2019 + 1 month = 9/31/2019 (which does dot exist!) 

Dax example using Dates = 
    DATE(
        YEAR([Snapshot Date]),
            MONTH([Snapshot Date])+1,
                DAY([Snapshot Date])
    )

 

For this reason, the Power Query route may be a better solution for you depending on your situation.