Forum Discussion
DAX CALENDARAUTO function returns empty table when used in Power BI Desktop
- 2 years ago
hi garythomannCoGC ,
try like:
Dates (CALENDARAUTO) =
VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
VAR _FirstDate =
IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
VAR _DateTable =
FILTER (
CALENDARAUTO(),
YEAR ( [Date] ) >= YEAR(_FirstDate) -- 'CALENDARAUTO'.[Date]
)
RETURN
ADDCOLUMNS(
_DateTable,
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Quarter", FORMAT ( [Date], "\QQ yyyy" )
)
hi garythomannCoGC ,
try like:
Dates (CALENDARAUTO) =
VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
VAR _FirstDate =
IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
VAR _DateTable =
FILTER (
CALENDARAUTO(),
YEAR ( [Date] ) >= YEAR(_FirstDate) -- 'CALENDARAUTO'.[Date]
)
RETURN
ADDCOLUMNS(
_DateTable,
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Quarter", FORMAT ( [Date], "\QQ yyyy" )
)