Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
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
CalendarFinal
There are other ways of building the Date table, e.g. Power Query scripts or tools like Bravo.
Hopefully that helps!
Regards,
Owen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |