Forum Discussion
DATEADD Issue
Hi,
I have an issue with DATEADD function. When I add -1 month it shows blank values.
When I use 0 works fine. But I need to use -1, -3 months , etc
_Rev Previous = CALCULATE([_Rev Actual],DATEADD('Calendar'[Date],0,MONTH))
Any idea ?
Thanks!
Hi cristianml
It looks like 'Calendar' might not be marked as a date table.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables#setting-your-own-date-table
If that's not the problem, please post back and a sanitised PBIX would be useful to help diagnose.Regards
Owen
Thanks for posting back! 🙂
The issue appears to be that the 'Calendar' tables is based on distinct dates from Actuals and Backlog, which appears to be one date per month.
A Date table in Power BI must include all dates in the range from earliest to latest date present, even if some of those dates don't exist in fact tables.
It's also good practice to include full financial years (and therefore full months), otherwise some time intelligence functions may give unexpected results.
Here is a proposed definition of Calendar that would cover complete Sep-Aug years, and you should be able to mark as a date table:
Calendar = VAR FYStartMonth = 9 // FY is Sep to Aug VAR Offset = MOD ( 13 - FYStartMonth, 12 ) VAR MinDate = MIN ( MIN ( Actuals[Date] ), MIN ( Backlog[Date] ) ) VAR MaxDate = MAX ( MAX ( Actuals[Date] ), MAX ( Backlog[Date] ) ) VAR MinDateFY = YEAR ( EOMONTH ( MinDate, Offset ) ) VAR MaxDateFY = YEAR ( EOMONTH ( MaxDate, Offset ) ) VAR CalendarStart = EDATE ( DATE ( MinDateFY, 1, 1 ), - Offset ) VAR CalendarEnd = EOMONTH ( DATE ( MAXDateFY, 12, 1 ), - Offset ) VAR CalendarFinal = CALENDAR ( CalendarStart, CalendarEnd ) RETURN CalendarFinalThere are other ways of building the Date table, e.g. Power Query scripts or tools like Bravo.
Hopefully that helps!
Regards,
Owen
3 Replies
Hi cristianml
It looks like 'Calendar' might not be marked as a date table.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables#setting-your-own-date-table
If that's not the problem, please post back and a sanitised PBIX would be useful to help diagnose.Regards
Owen
hI OwenAuger ,
It doesn't allow me to mark as a date table. It says date column can't have gaps in dates. But I don't see gaps there:
Thanks for posting back! 🙂
The issue appears to be that the 'Calendar' tables is based on distinct dates from Actuals and Backlog, which appears to be one date per month.
A Date table in Power BI must include all dates in the range from earliest to latest date present, even if some of those dates don't exist in fact tables.
It's also good practice to include full financial years (and therefore full months), otherwise some time intelligence functions may give unexpected results.
Here is a proposed definition of Calendar that would cover complete Sep-Aug years, and you should be able to mark as a date table:
Calendar = VAR FYStartMonth = 9 // FY is Sep to Aug VAR Offset = MOD ( 13 - FYStartMonth, 12 ) VAR MinDate = MIN ( MIN ( Actuals[Date] ), MIN ( Backlog[Date] ) ) VAR MaxDate = MAX ( MAX ( Actuals[Date] ), MAX ( Backlog[Date] ) ) VAR MinDateFY = YEAR ( EOMONTH ( MinDate, Offset ) ) VAR MaxDateFY = YEAR ( EOMONTH ( MaxDate, Offset ) ) VAR CalendarStart = EDATE ( DATE ( MinDateFY, 1, 1 ), - Offset ) VAR CalendarEnd = EOMONTH ( DATE ( MAXDateFY, 12, 1 ), - Offset ) VAR CalendarFinal = CALENDAR ( CalendarStart, CalendarEnd ) RETURN CalendarFinalThere are other ways of building the Date table, e.g. Power Query scripts or tools like Bravo.
Hopefully that helps!
Regards,
Owen
