Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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" )
)
Solved! Go to Solution.
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" )
)
Thank you @FreemanZ
DAX CALENDARAUTO function returns empty table when used in Power BI Desktop
If you would like more kudos
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" )
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
125 | |
32 | |
27 | |
24 | |
23 |