Blog

Microsoft Analysis Services – The DAX Function “DATEADD” doesn’t work correctly like I want

The data of the column “TimeByDay” in SSAS is the source from SQL Database. I would like to create a new column “FilterYearNumber”. It will get information from the column “TimeByDay” with the DAX function “DATEADD”.

The formula for the column”FilterYearNumber” is:

Now you can see some data in the column “FilterYearNumber”.

Some fields are empty. I recognized it depends on the date. Some days it shows empty fields. So I add a new column “FirstDayTimeByDay” and it always displays the first day of the month.

The formula is:

Now the column “FilterYearNumber” uses the column “FirstDayTimeByDay” instead of “TimeByDay” for calculation. The formula is: