DAX - DATEADD Function

[dhamodarankms]

Dear Power BI Community,

I am Dhamo, Power BI Reports and Dashboards developer along with Data engineering and Modeleling. Today, I am doing a Date Model and found a wired situation while used DAX command called DATE ADD. It is working fine for the DAY but not for MONTH, QUATER, and MONTH. Much appreciate suggestions and knowledge in this occassion. 

DATEADD(DimDate[Date],-1,DAY) - Logic for Previous Day - Working fine

DATEADD(DimDate[Date],-1,YEAR) - Logic for Previous Year- It is not generating value and not returning with errors

DATEADD(DimDate[Date],-1,MONTH) - Logic for Previous Month - It is not generating value and not returning with errors

DATEADD(DimDate[Date],-1,QUATER) - Logic for Previous Quater- It is not generating value and not returning with errors

I have tried with PREVIOUS MONTH | YEAR | QUATER functions and those are also not handy.

Regards,

SD

[vivran22]

Hello @dhamodarankms ,

DATEADD function requires a date table which should include all the days within one year. A complete date table is of great importance for DATEADD to behave as expected.

DATEADD function uses three rules:

1. It only returns days that exists in the date column. If some expected dates are missing, then it returns only those dates that are not missing in the date column
2. If a date does not exist in the corresponding month after the shifting operation, then the result of DATEADD includes the last day of the corresponding month.
3. If the selection includes the last two days of a month, then the result of DATEADD includes all the days between the corresponding days in the shifted month and the end of the shifted month.

 Alternatively, you can use PARALLELPERIOD.

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[dhamodarankms]

Thanks for your extended support in the regard Vivek. I will text your solution and get back to you with my understanding. 

You Welcome.

Regards,

SD

[Anonymous]

If you navigate to dax.guide/dateadd/, you'll find that one of the sentences in the description of the function says:

The result table includes only dates that exist in the dates column.

Does this explain the behaviour you see?

[Greg_Deckler]

@dhamodarankms You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Specifically maybe this: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/m-p/1259467#M583

[amitchandak]

@dhamodarankms , dateadd work with continuous dates. Means you need to have date in your date table for that. Make sure you have all dates in date table.

 You can also use date for day, month and year as date add

example

Today = TODAY()
Month Back Date = Var _DT = Today() 
return Date(year(_DT),Month(_DT)-1,Day(_DT))

Year, Month, day Back Date = Var _DT = Today() 
return Date(year(_DT)-1,Month(_DT)-10,Day(_DT)-1)

refer

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

[dhamodarankms]

Thanks. I am using computed date table and the dates are contnious. I am wondering the logic is working fine for Day operations but not for the rest. Yes, it is always good to go with DAX Query instead of Simple DAX expressions. I thought that, the DAX expressions are much powerfull and not having any restrictions by comparing to DAX Queries & Statements.