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 :

=FORMAT(DATEADD(KfW_AssignmentByDay[TimeByDay];-1;month);”MMyyyy”)

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:

=DATE(YEAR(KfW_AssignmentByDay[TimeByDay]);MONTH(KfW_AssignmentByDay[TimeByDay]);1)


Now the column “FilterYearNumber” uses the column “FirstDayTimeByDay” instead of “TimeByDay” for calculation.

The formula is:

=FORMAT(DATEADD(KfW_AssignmentByDay[FirstDayTimeByDay];-1;month);”MMyyyy”)

The final result are here:

Leave a Reply

Your email address will not be published. Required fields are marked *