topic DAX CALENDARAUTO function returns empty table when used in Power BI Desktop in DAX Commands and Tips

DAX CALENDARAUTO function returns empty table when used in Power BI Desktop

garythomannCoGC — Wed, 13 Mar 2024 00:43:45 GMT

Doh, followed along with the calendarauto example from the video (sqlbi.com) but no dates produced.

No syntax errors. Using 07.40 - Moving average.pbix

Table of Dates = CALENDARAUTO()

works just fine.

Tried other pbix files as well. 'New table' button was used :}

The following is the code I created that produces no syntax errors and no dates

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] ) >= _FirstDate -- 'CALENDARAUTO'.[Date]
)
RETURN
ADDCOLUMNS(
_DateTable,
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Quarter", FORMAT ( [Date], "\QQ yyyy" )
)

Re: DAX CALENDARAUTO function returns empty table when used in Power BI Desktop

FreemanZ — Wed, 13 Mar 2024 01:39:05 GMT

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" )
)

Re: DAX CALENDARAUTO function returns empty table when used in Power BI Desktop

garythomannCoGC — Wed, 13 Mar 2024 02:44:28 GMT

Thank you FreemanZ

DAX CALENDARAUTO function returns empty table when used in Power BI Desktop

If you would like more kudos