DateAdd Give Blank Results For Subtracting Months

[jtoner]

The DATEADD function only works when going forward in time. When I try to use it going backwards in time it gives blank results. Everything I read online says I should be able to use it to subtract dates, but it is not working properly. I have tried using "PREVIOUSMONTH" function as well with the same results.

I have spent hours trying everything to get this to work with nothing to show for it...

[v-jingzhang · Accepted solution]

Hi @jtoner 

DATEADD and PREVIOUSMONTH are both Time Intelligence functions. The result table includes only dates that exist in the dates column. If 2022 Jan is the earliest month in your Query1[Today] column, when you use these functions to get previous month, it returns blank as there are no previous month dates in Today column. 

PREVIOUSMONTH - DAX Guide

DATEADD - DAX Guide

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ALLUREAN]

Hi, @jtoner 

Try to remove the .[Date] and use clean date column and maybe better use a measure than calculated column for time intelligence patterns.

LastMonthMeasure = CALCULATE([YouMeasure], DATEADD(Query1[Today], -1, MONTH)

[bcdobbs]

Is the earliest month in your original table Jan 2022?

DATEADD can only return dates that exist in a date table. You're using the auto table which will contain dates from 1 Jan 2022 to 31 Dec of last year present.

You could trick it by creating a table with some 2021 dates. However best to create a custom date table:

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/