DATEADD Issue

[cristianml]

Hi,

 I have an issue with DATEADD function. When I add -1 month it shows blank values.

_Rev Previous = CALCULATE([_Rev Actual],DATEADD('Calendar'[Date],-1,MONTH))

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!

[OwenAuger · Accepted solution]

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-...

If that's not the problem, please post back and a sanitised PBIX would be useful to help diagnose.

Regards

Owen

[OwenAuger · Accepted solution]

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-...

If that's not the problem, please post back and a sanitised PBIX would be useful to help diagnose.

Regards

Owen

[cristianml]

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:

[OwenAuger · Accepted solution]

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
    CalendarFinal

There are other ways of building the Date table, e.g. Power Query scripts or tools like Bravo.

Hopefully that helps!

Regards,

Owen